How can I sum strings in one column into one row in Microsoft SQL? Group by does not work since it is MSSQL

Question

for example we have table with three columns

Column A |  Column B | Column C
-------------------------------
P1       |    10     |    A    
P1       |    20     |    B
P1       |    30     |    C
P2       |    30     |    D
P2       |    40     |    E

How do I have to write SELECT query in Microsoft SQL to have this kind of result

Column A |     Column B      |   Column C
------------------------------------------
P1       |    10, 20, 30     |    A, B, C
P2       |    30, 40         |    D, E

Possible duplicate of [How to concatenate many rows with same id in sql?](https://stackoverflow.com/questions/35172956/how-to-concatenate-many-rows-with-same-id-in-sql) — crimson589, Dec 07 '17 at 07:01
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Ullas, Dec 07 '17 at 07:11

score 5 · Answer 1 · answered Dec 07 '17 at 07:35

5

If you are running SQL Server 2017, T-SQL enhancements introduced to SQL developers include the STRING_AGG string concatenation function

You can simply use following SELECT statement for aggregating string values from different rows into a single column value

select
    ColumnA,
    string_agg(ColumnB,',') ColumnB,
    string_agg(ColumnC,',') ColumnC
from TestTable1
group by ColumnA

answered Dec 07 '17 at 07:35

Eralper

6,461
2
21
27

I am running Sql server 2014 :( Thanks for the tip. I will keep this in mind – TheOneAndOnlyNoobofCSharp Dec 07 '17 at 09:07

score 4 · Accepted Answer · answered Dec 07 '17 at 08:50

try this

CREATE TABLE #temp(ColumnA VARCHAR(10),ColumnB INT ,ColumnC VARCHAR(10))
--insert rows

SELECT t.[ColumnA] ,
         [ColumnB] = STUFF((SELECT  ','+cast([ColumnB] AS VARCHAR ) FROM  #Temp WHERE  [ColumnA]  = t.[ColumnA]  FOR XML PATH('')),1,1,''),
         [ColumnC] = STUFF((SELECT  ','+[ColumnC] FROM #Temp WHERE [ColumnA] = t.[ColumnA] FOR XML PATH('')),1,1,'')
FROM #Temp t
GROUP BY t.[ColumnA]

i test it

score 1 · Answer 3 · answered Dec 07 '17 at 07:04

You could try xml method with stuff() function

select t.[Column A] ,
         [Column B] = 
                stuff(
                       (select  ','+cast([Column B] as varchar) from <table> where [Column A]  = t.[Column A]  for xml path('')),
                       1,1,''),
         [Column C] = stuff(
                       (select  ','+[Column C] from <table> where [Column A] = t.[Column A] for xml path('')),
                       1,1,'')
from <table> t
group by t.[Column A]

score 1 · Answer 4 · answered Dec 07 '17 at 07:27

SELECT DISTINCT COLA,
      STUFF((select ', '+ColB from T1 AS B where A.ColA=B.ColA FOR XML PATH(''),Type).value('.','NVARCHAR(MAX)'),1,2,'') AS ColB,
      STUFF((select ', '+ColC from T1 AS B where A.ColA=B.ColA FOR XML PATH(''),Type).value('.','NVARCHAR(MAX)'),1,2,'') AS ColC

      FROM T1 A

How can I sum strings in one column into one row in Microsoft SQL? Group by does not work since it is MSSQL

4 Answers4