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

• Erik Reasonable Rates Darling: To get help writing a query, check out this Q&A.
• RDFozz: Also, please specify your version of SQL Server, as the options are different for different versions.

Answer 1

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

• jmoreno: 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.