If you want, I have already created the assembly, and it is possible to download the DLL file here. The one I think is very well explained is this one. There are a lot of sites that explain how to create it. This answer will require some privilege on the server to work.Īssemblies are a good option for you. INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1 SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1 ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw, Changing it to match all rows in the table is left as an exercise for the reader. Note that although the results match the question, the data doesn't quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. The code below is an example of a recursive CTE. Easily fixed with one of two solutions:ĭepending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message ).Ī recursive CTE solution was suggested, but no code was provided. The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make NULL after that row, and the next row will start over as an empty string again.Coalesce is really just a helpful cheat that accomplishes two things:ġ) No need to initialize with an empty string value.Ģ) No need to strip off an extra separator at the end.Just some explanation (since this answer seems to get relatively regular views): This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers. 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |