t-sql most efficient row to column? crosstab for xml path, pivot
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
CREATE CLUSTERED INDEX IX_CL__orders on _orders(OrderId)
CREATE NONCLUSTERED INDEX IX_NCL__orders on _orders(OrderId)
INCLUDE (CustomerId)
CREATE CLUSTERED INDEX IX_CL_details on _details(OrderId)
CREATE NONCLUSTERED INDEX IX_NCL_details on _details(OrderId)
INCLUDE (DetailId,ProductId)
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
By definition a PIVOT is going to HAVE to aggregate in some way, because you can have multiple rows with the same pivot key columns. If you don't have multiple rows, that's fine - but you still need to choose an aggregate operator (MIN, MAX, SUM).
But the FOR XML PATH
construct is better for the multiple-row-values to single-string-column "pivot" operation.
I'm not sure why yours is not performing well. What indexes do you have on the tables? What does your execution plan look like?
精彩评论