rolling up multiple rows in a single row in a colum order in sql server

Question

How can I return multiple rows with the same values into a single row as comma separated and combined in ascendant order in a single column (in SQL server)?

Table2

------------
col1 | col2 | col3
----------------------
1    | line1 | 2
1    | line2 | 1
1    | line3 | 4
2    | line4 | 1
2    | line5 | 3
2    | line6 | 2  
3    | line7 | 2
3    | line8 | 1

Desired result in ascendant order according to col3:

Col1 |  col2
----------------------------
1    | Line2,Line1,Line3
2    | Line4,Line6,Line5
3    | Line8,Line7

which RDMS are you using? And it doesn't look like you are using table 2? — kjmerf, Sep 11 '19 at 14:07
Possible duplicate of [Formatting of sql server queries](https://stackoverflow.com/questions/57885737/formatting-of-sql-server-queries) — Rob Streeting, Sep 11 '19 at 14:13
Now it is more clear I think, I have updated the question :) — db_noob, Sep 11 '19 at 14:13
What is your rdbms? Sql Server, postgres, oracle? What version? — Juan Carlos Oropeza, Sep 11 '19 at 14:16
Curious, why do you want comma separated values *within* a column? How does that help in any reporting or analysis needs. — Parfait, Sep 11 '19 at 14:33
Good question. I need to locate the ones that have no data as a category for a report. — db_noob, Sep 11 '19 at 14:58

Neeraj Agarwal · Accepted Answer · 2019-09-11T15:37:52.550

1

Hopefully this is what you are looking for:

select Col1, string_agg(Col2, ',') within group(order by Col3)
from Table2
group by Col1

edited Sep 11 '19 at 15:37

answered Sep 11 '19 at 15:32

Neeraj Agarwal

1,059
6
5

@SQL_NewUser It is working for me. Maybe you want desc sort order, in which case you can make the following change (order by Col3 desc). – Neeraj Agarwal Sep 11 '19 at 16:48

rolling up multiple rows in a single row in a colum order in sql server

1 Answers1