Comma seperated value in select with order by in SQL Server

Question

This is probably a very common question but maybe with another twist.

I have two tables which I join and want to get comma seperated values of a particular column of second table

  select name,(Comma seperated scores) 'Scores' from Person Inner join Score 
  on Person.Id=Score.PersonId

  Example Output 

  name    Scores
  User1   zd,ad,cm

I have read that It can be done by having another function, but probably that's not very performance oriented also I want the Scores strings to be ordered by in ascending order. i.e ad,cm,zd

Is there a way to do it by continuing to use only Inner Joins ?

What database engine? mySQL (`group_Concat()`), SQL SERVER (`List_Agg()` if correction version or `for XML path`), Oracle (`List_Agg()`, or `WM_CONCAT()`) depending on version? Most support an order by within the aggregrate function. — xQbert, Jul 03 '17 at 14:14
The answer depends on the database you are using. You can probably google: " group_concat()" for answers for your database. — Gordon Linoff, Jul 03 '17 at 14:14
@dasblinkenlight . . . This question is not about *storing* such values; it is about returning them. And people being, well, human, sometimes like to see things in that format. — Gordon Linoff, Jul 03 '17 at 14:25

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Try something like this:

SELECT name
      ,STUFF(
        (SELECT ',' + score 
         FROM Score AS s 
         WHERE s.PersonId=p.PersonId 
         FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,1,'') AS CSV_Scores
FROM Person AS p;

The query will call the rows from table Person and then use FOR XML PATH() to get a comma separated string. STUFF is used to cut away the first comma.

UPDATE: stand-alone example with `ORDER BY Score`:

DECLARE @Person TABLE(PersonID INT IDENTITY,Name VARCHAR(100));
INSERT INTO @Person VALUES('Jim'),('Jane');
DECLARE @Score TABLE(ScoreID INT IDENTITY, PersonID INT,Score VARCHAR(100));
INSERT INTO @Score VALUES(1,'ba'),(1,'db'),(1,'ac')
                        ,(2,'ba'),(2,'ab');  

SELECT name
      ,STUFF(
        (SELECT ',' + score 
         FROM @Score AS s 
         WHERE s.PersonId=p.PersonId 
         ORDER BY s.Score
         FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,1,'') AS CSV_Scores
FROM @Person AS p;

The result

name    CSV_Scores
Jim     ac,ba,db
Jane    ab,ba

exactly what i wanted.thnks – Mandar Jogalekar Jul 04 '17 at 04:09 — Mandar Jogalekar, Jul 04 '17 at 04:09

Comma seperated value in select with order by in SQL Server

1 Answers1

UPDATE: stand-alone example with ORDER BY Score:

UPDATE: stand-alone example with `ORDER BY Score`: