SQL Multiple query results into one field

Question

question

jwec asked Feb 25, '23

SQL Multiple query results into one field

My JobMaster table has the following data:

SalesOrder Line Job

50123 1 1000

50123 2 1010

50123 3 1012

50123 4 1015

50301 1 1061

50301 2 1064

50444 1 1072

50444 2 1076

A normal select would present the data as it appears above.

I need is the resulting data to appear as:

50123 1000, 1010, 1012, 1015

50301 1061, 1064

50444 1072, 1076

sqlquery

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2023-02-27T11:07:39Z

anthony.green answered Feb 27, '23

What version of SQL are you running?

STRING_AGG would be your friend here

create table #data (salesorder int, line int, job int)
go
insert into #data values
(50123,1,1000),
(50123,2,1010),
(50123,3,1012),
(50123,4,1015),
(50301,1,1061),
(50301,2,1064),
(50444,1,1072),
(50444,2,1076)
go
select salesorder, STRING_AGG(job,',') from #data
group by salesorder

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question