t-sql 最有效的行到列？ xml 路径、枢轴的交叉表

Question

我正在寻找将行转换为列的最有效的方法。我需要以固定宽度和分隔格式输出数据库的内容（不是下面的实际模式，但概念相似）。下面的 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

我研究过数据透视表，但我发现的大多数示例都是聚合信息。我只想合并子行并将它们附加到父行上。

我还应该指出，我不需要处理列名称，因为子行的输出将是固定宽度字符串或分隔字符串。

例如，给定下表：

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。

设置示例：

   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：

 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 小时。

orderid     Products
----------- -----------------------
1             100  158  234
2             125
3             101  105  212  250

Answer 1

根据定义，数据透视表必须以某种方式聚合，因为您可以拥有具有相同数据透视表键列的多行。如果您没有多行，那很好 - 但您仍然需要选择聚合运算符（MIN、MAX、SUM）。

但是 FOR XML PATH 构造更适合多行值到单字符串列的“透视”操作。

我不确定为什么你的表现不好。表上有哪些索引？您的执行计划是什么样的？