SELECT DISTINCT Query to string list

Question

How can I covert single column from SELECT DISTINCT Query into a string with list of query results separated by commas.

My SQL Query example:

SELECT DISTINCT FirstName FROM CompanyA WHERE DIV_Num in ('SUB.EAST', 'SUB2', 'SUB3', 'HR.SUB', 'HR.SUB2')

Output example:

Bob
Alice
Jim

I would like to have result in single string:

Bob, Alice, Jim

You've tagged as asp.net and SQL server. Do you need an answer in T-SQL, or can this be done in C# on the server side? String manipulation like this is not SQL server's strong point - prob better done in C#. — Paddy, Jan 02 '14 at 15:45

score 1 · Accepted Answer · answered Jan 02 '14 at 16:11

An Example

Select distinct ST2.SubjectID, 
           substring((Select ','+ST1.StudentName  AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')),2, 1000) [Students]
     From dbo.Students ST2

SELECT DISTINCT Query to string list

1 Answers1