0

I have this code from the previous post.

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

I need to get distinct values in each comma separated list. I am getting the same name multiple times. So the ST1.StudentName+',' AS [text()] might have repeats in the list and I want to eliminate them.

Dale K
  • 25,246
  • 15
  • 42
  • 71
SLee
  • 13
  • 4
  • Here is the link to the first post https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv – SLee Sep 22 '22 at 18:36
  • All *supported* versions of SQL Server have `STRING_AGG`. What you posted is a trick used in older versions, which returns a query's results as XML with an empty tag name `PATH ('')`. The result of this inner query is a single string. `DISTINCT` needs to go to that *inner* query `SELECT DISTINCT ST1.StudentName + ',' ...` – Panagiotis Kanavos Sep 22 '22 at 18:40
  • Thanks, I did have to get rid of the order by clause I can't believe I missed that. Unfortunately, our server is not 2017 so I don't have STRING_AGG – SLee Sep 22 '22 at 18:45
  • SQL Server 2017 is the oldest version still in mainstream support. In previous versions the *fastest* way is to use a SQLCLR function. The XML technique is the second fastests. In SQL Server 2016 you can use `FOR JSON` instead of `FOR XML` – Panagiotis Kanavos Sep 22 '22 at 18:46

0 Answers0