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!
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments