SQL Server：将行转换为列（枢轴，但有一个扭曲）

I'm trying to compress multiple rows of a table into 1 row but multiple columns.

我正在尝试将表的多行压缩为1行但多列。

I looked at a few methods but none of them seem to be right for what I need. The (main) problem being there is no 'heading' data in the table which ruins the pivot method.

我看了几个方法，但似乎没有一个方法适合我的需要。（主要）问题是表中没有“标题”数据，这会破坏枢轴方法。

Basically I have 2 tables I am trying to join, and I am trying to get only the first 3 items (anything more than 3 I don't care about).

基本上我有2张桌子我想加入，而我只想获得前3个项目（超过3个我不关心的东西）。

So my 2 tables are created and filled.

所以我的2个表格被创建和填充。

CREATE TABLE [order] ( ID INT )
CREATE TABLE OrderItem ( OrderID INT, Item VARCHAR(20) )

insert into [order] values (1)
insert into [order] values (2)

insert into orderitem values (1, 'Hammer')
insert into orderitem values (2, 'Spoon')
insert into orderitem values (2, 'Potato')
insert into orderitem values (2, 'shed')

And I want my results like this:

我希望我的结果如下：

ID  Item1   Item2   Item3  
1   Hammer  NULL    NULL  
2   Potato  shed    Spoon

I've looked at a few methods of using PIVOT, but because I don't have any 'headings' for each value, and because there are many thousands of possible unique [Item] values in [OrderItem] I can't figure out how to get a query to do what I want.

我已经看了一些使用PIVOT的方法，但因为我没有为每个值设置任何“标题”，并且因为[OrderItem]中有数千个可能的唯一[Item]值，我无法弄清楚如何获取查询以执行我想要的操作。

The only way I have managed to do it with some ugly ranking joins - which works great but it is hideously slow.

我设法用一些丑陋的排名连接来实现它的唯一方法 - 效果很好，但速度非常慢。

(the query without the joins takes a fraction of a second to complete, but takes upwards of 2-3 minutes once they are included because of the amount of data its looking at)

（没有连接的查询只需要几分之一秒即可完成，但由于其查看的数据量，一旦包含它们，则需要花费2-3分钟）

SELECT 
    o.ID, i1.Item, i2.Item, i3.Item 
FROM
    [order] o
LEFT OUTER JOIN
    (SELECT
         *, 
         RANK() OVER (PARTITION BY orderId ORDER BY item) AS iRank
     FROM 
         OrderItem) AS i1 ON i1.OrderID = o.ID AND i1.iRank = 1
LEFT OUTER JOIN
    (SELECT
         *, 
         RANK() OVER (PARTITION BY orderId ORDER BY item) AS iRank
     FROM 
         OrderItem) AS i2 ON i2.OrderID = o.ID AND i2.iRank = 2
LEFT OUTER JOIN
    (SELECT
         *, 
         RANK() OVER (PARTITION BY orderId ORDER BY item) AS iRank
     FROM 
         OrderItem) AS i3 ON i3.OrderID = o.ID AND i2.iRank = 3

Can anyone recommend a better method to look at that won't completely kill my database when it is being run?

任何人都可以推荐一个更好的方法来查看它在运行时不会完全杀死我的数据库吗？

2 个解决方案

#1

Although you can do this with pivot, it might be easier to just use conditional aggregation:

虽然您可以使用pivot执行此操作，但使用条件聚合可能更容易：

select oi.OrderId,
       max(case when iRank = 1 then oi.Item end) as item1,
       max(case when iRank = 2 then oi.Item end) as item2,
       max(case when iRank = 3 then oi.Item end) as item3
from (select oi.*,
             rank() over (partition by orderId order by item) as iRank
      from OrderItem oi
     ) oi 
group by oi.OrderId;

#2

Update- Since the number of order items are indeterminate, I converted this into a dynamic PIVOT

更新 - 由于订单商品的数量不确定，我将其转换为动态PIVOT

DECLARE @qu NVARCHAR(MAX), @pcol NVARCHAR(MAX)
SELECT   @pcol= COALESCE(@pcol + ',','') + ItemNumber FROM
 (SELECT DISTINCT N'Item'+ CAST (ROW_NUMBER() OVER(PARTITION BY OrderID Order by Item) AS NVARCHAR(25)) AS ItemNumber 
  FROM OrderItem) A

SET @qu=N'SELECT OrderId,'+ @pcol + N' FROM 
(
  SELECT 
  OrderID,N''Item''+ CAST (ROW_NUMBER() OVER(PARTITION BY OrderID Order by Item) AS NVARCHAR(25)) AS ItemNumber, Item FROM OrderItem
  )S
  PIVOT
  (
  MAX(Item) 
    FOR ItemNumber IN ('+@pcol +N')) AS piv'
EXEC sp_executesql @qu

Update SQL fiddle link:http://sqlfiddle.com/#!6/0cd9d0/17

更新SQL小提琴链接：http：//sqlfiddle.com/#！6 / 0cd9d0 / 17

EDIT - Original Answer:

编辑 - 原始答案：

Using Pivot for known maximum number of order items, this can be done like

使用Pivot获取已知的最大订单商品数量，可以这样做
SELECT OrderId,[Item1],[Item2],[Item3],[Item4],[Item5],[Item6]
FROM
(
  SELECT 
  OrderID,N'Item'+ CAST (ROW_NUMBER() OVER(PARTITION BY OrderID Order by Item) AS NVARCHAR(25)) AS ItemNumber, Item FROM OrderItem
  )S
  PIVOT
  (
  MAX(Item) 
    FOR ItemNumber IN ([Item1],[Item2],[Item3],[Item4],[Item5],[Item6])
  )piv
Sql fiddle link : http://sqlfiddle.com/#!6/0cd9d0/4

Sql小提琴链接：http：//sqlfiddle.com/#！6/0cd9d0/4

#1