You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query: SELECT DISTINCT ST2.SubjectID, LEFT(Main.Students,Len(Main.Students)-1) As "Students" I used the following T-SQL: SELECT Main.SubjectID, Result I expected was: SubjectID StudentName If there is a table called STUDENTS SubjectID StudentName In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily. I had a similar issue when I was trying to join two tables with one-to-many relationships. If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
0 Comments
Leave a Reply. |