MS SQL Concatenate multiple values into a single column

Question

I currently have the following table;

Invoice   Client   Purchase Order
1000      A1       1234
1000      A1       1235
1001      B2       1236
1001      B2       1237
1002      B2       1238

and I'm looking for a quick way to get to;

Invoice   Client   Purchase Orders
1000      A1       1234 1235
1001      B2       1236 1237
1002      B2       1238

Any help would be appreciated!

Tag the DBMS you are using. The answer to this is depends on it. — sticky bit, Jul 06 '18 at 11:26
I hope you are not planning on saving the grouped values in a column, right? — EzLo, Jul 06 '18 at 11:26
Voting to close as unclear what you are asking, because without knowing your database, we can't help you. But, your question is probably a duplicate of several others anyway. — Tim Biegeleisen, Jul 06 '18 at 11:27
Asked and answered a thousand times. https://stackoverflow.com/questions/tagged/string-aggregation+sql — , Jul 06 '18 at 11:30

score 1 · Accepted Answer · edited Jul 06 '18 at 13:11

As per your given details, assuming a table #temp below, with sample data:

create table #temp (
     invoice int,
     client varchar(5),
    [purchase order]  int
)

insert into #temp
select 1000,'A1',1234  union all
select 1000,'A1',1235  union all
select 1001,'B2',1236  union all
select 1001,'B2',1237  union all
select 1002,'B2',1238

Now you can use below query using FOR XML as per your required output:

select distinct tp1.invoice,tp1.client,  
(  
    SELECT convert(varchar(10),[purchase order]) + ' ' as [text()]  
    from #temp tp  
    where tp.invoice=tp1.invoice and tp.client=tp1.client  
    for XML path('')  
) as [purchase order]  
from #temp tp1

If you have any query, let me know.

MS SQL Concatenate multiple values into a single column

1 Answers1