Need help in creating a query

Question

I have a table with below data.

columnA  columnB
ONE      value 1
ONE      value 2
ONE      value 3
TWO      value 1
TWO      value 2
THREE    value 1

Need help in creating a query which gives below

COLUMN X   COLUMN Y
ONE         value 1, value 2, value 3
TWO         value 1, value 2
THREE       value 1

Also, please specify your version of SQL Server, as the options are different for different versions. — RDFozz, Jun 27, 2018 at 22:16

Kin Shah · Accepted Answer · 2018-06-27 20:54:43Z

6

What you are needing help is called Grouped Concatenation in SQL Server

This sql fiddle that I created will help you.

Basically you use stuff + FOR XML PATH.

If you are on sql server 2017 (vNEXT) then STRING_AGG is an inbuilt function that you can use

-- sql server vNEXT

SELECT [ColumnA]  =  [ColumnA],
       [ColumnBs] = STRING_AGG(ColumnB, ',')
 from dbo.Table1   
 group by ColumnA

answered Jun 27, 2018 at 20:54

Kin Shah

62.1k6 gold badges119 silver badges235 bronze badges

Case when row_number() over (order by columnb) = 1 then '' else ',' end + columnB is more understandable than stuff. Or use lag/lead if you want to do different things for the first and last.
– jmoreno
Jun 28, 2018 at 0:38

Add a comment |

1 Answer 1