1

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
2
  • To get help writing a query, check out this Q&A. Jun 27 '18 at 20:49
  • 1
    Also, please specify your version of SQL Server, as the options are different for different versions.
    – RDFozz
    Jun 27 '18 at 22:16
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
1
  • 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 '18 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.