T-SQL最有效的行到列？ XML路径的交叉表，枢轴 [英] t-sql most efficient row to column? crosstab for xml path, pivot
问题描述
我正在寻找将行变成列的最有效方法。我需要以固定宽度和定界格式输出db的内容（以下不是实际的架构，但概念相似）。下面的FOR XML PATH查询为我提供了我想要的结果，但是当处理少量数据以外的其他内容时，可能需要一段时间。
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.
例如，给定下表：
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
我需要输出的订单：
orderid Products
----------- -----------------------
1 100 158 234
2 125
3 101 105 212 250
或
orderid Products
----------- -----------------------
1 100|158|234
2 125
3 101|105|212|250
是想法还是建议？我正在使用SQL Server 2k5。
Thoughts or suggestions? I am using SQL Server 2k5.
示例设置：
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)
使用FOR XML PATH：
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
这会输出我想要的内容，但是处理大量数据的速度非常慢。其中一个子表超过200万行，使处理时间延长到大约4小时。
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
推荐答案
按定义a PIVOT将需要以某种方式进行聚合，因为您可以有多个具有相同枢轴键列的行。如果没有多行，那很好-但是您仍然需要选择一个聚合运算符（MIN，MAX，SUM）。
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).
但是 FOR XML PATH 构造对于单行列数据透视操作的多行值更好。
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?
这篇关于T-SQL最有效的行到列？ XML路径的交叉表，枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
