t-sql most efficient row to column? crosstab for xml path, pivot
- by ajberry
I am looking for the most performant way to turn rows into columns. I have a requirement to output the contents of the db (not actual schema below, but concept is similar) in both fixed width and delimited formats. The below FOR XML PATH query gives me the result I want, but when dealing with anything other than small amounts of data, can take awhile.
select orderid
,REPLACE(( SELECT ' ' + CAST(ProductId as varchar)
FROM _details d
WHERE d.OrderId = o.OrderId
ORDER BY d.OrderId,d.DetailId
FOR XML PATH('')
),' ','') as Products
from _orders o
I've looked at pivot but most of the examples I have found are aggregating information. I just want to combine the child rows and tack them onto the parent.
I should also point out I don't need to deal with the column names either since the output of the child rows will either be a fixed width string or a delimited string.
For example, given the following tables:
OrderId CustomerId
----------- -----------
1 1
2 2
3 3
DetailId OrderId ProductId
----------- ----------- -----------
1 1 100
2 1 158
3 1 234
4 2 125
5 3 101
6 3 105
7 3 212
8 3 250
for an order I need to output:
orderid Products
----------- -----------------------
1 100 158 234
2 125
3 101 105 212 250
or
orderid Products
----------- -----------------------
1 100|158|234
2 125
3 101|105|212|250
Thoughts or suggestions? I am using SQL Server 2k5.
Example Setup:
create table _orders (
OrderId int identity(1,1) primary key nonclustered
,CustomerId int
)
create table _details (
DetailId int identity(1,1) primary key nonclustered
,OrderId int
,ProductId int
)
insert into _orders (CustomerId)
select 1
union select 2
union select 3
insert into _details (OrderId,ProductId)
select 1,100
union select 1,158
union select 1,234
union select 2,125
union select 3,105
union select 3,101
union select 3,212
union select 3,250
using FOR XML PATH:
select orderid
,REPLACE(( SELECT ' ' + CAST(ProductId as varchar)
FROM _details d
WHERE d.OrderId = o.OrderId
ORDER BY d.OrderId,d.DetailId
FOR XML PATH('')
),' ','') as Products
from _orders o
which outputs what I want, however is very slow for large amounts of data. One of the child tables is over 2 million rows, pushing the processing time out to ~ 4 hours.
orderid Products
----------- -----------------------
1 100 158 234
2 125
3 101 105 212 250
