SQL Server高级Pivot将行分组为列

Question

我需要将行中的数据旋转/旋转到列中 - 但与我见过的大多数示例有点不同。

我们有顾客会买东西（想想披萨配料卖家......人们总是会买奶酪，面团和酱汁;有些人可能会购买配料，但我们不关心）。

我需要做的是按订单日期对列数据进行排序。下面是两个脚本来填补温度输入和温度输出表，以显示我想要实现的目标。

SQL Server 2008的

CREATE table #myInput 
    (CustomerID Varchar(10), OrderDate varchar(10), Item varchar(13), ItemColor varchar(20)); 


CREATE table #myOUTPUT 
    (
    CustomerID Varchar(10), 
    OrderDate_1 varchar(10), 
    PartA_1 varchar(20), 
    PartB_1 varchar(20), 
    PartC_1 varchar(20), 
    OrderDate_2 varchar(10), 
    PartA_2 varchar(20), 
    PartB_2 varchar(20), 
    PartC_2 varchar(20), 
    OrderDate_3 varchar(10), 
    PartA_3 varchar(20), 
    PartB_3 varchar(20), 
    PartC_3 varchar(20) 
    ) 
INSERT INTO #myInput 
    (CustomerID, OrderDate, Item, ItemColor) 
VALUES 
('abc','5/1/2001','PartA','Silver'), 
('abc','5/1/2001','PartB','Red'), 
('abc','5/1/2001','PartC','Green'), 
('abc','5/20/2002','PartA','Purple'), 
('abc','5/20/2002','PartB','Yellow'), 
('abc','5/20/2002','PartC','Black'), 
('abc','10/1/2002','PartA','Red'), 
('abc','10/1/2002','PartB','Silver'), 
('abc','10/1/2002','PartC','Blue'), 
('def','4/1/2000','PartA','Green'), 
('def','4/1/2000','PartB','Red'), 
('def','4/1/2000','PartC','White'), 
('jkl','5/1/2001','PartA','Black'), 
('jkl','5/1/2001','PartB','Yellow'), 
('jkl','5/1/2001','PartC','Silver'), 
('jkl','10/10/2001','PartA','Green'), 
('jkl','10/10/2001','PartB','Black'), 
('jkl','10/10/2001','PartC','Silver') 
; 

而结果：

insert into #myOUTPUT 

（客户ID，OrderDate_1，PartA_1，PartB_1，PartC_1，OrderDate_2，PartA_2，PartB_2，PartC_2，OrderDate_3，PartA_3，PartB_3， PartC_3）

VALUES 
('abc','5/1/2001','Silver','Red','Green','5/20/2002','Purple','Yellow','Black','10/1/2002','Red','Silver','Blue'), 
('def','4/1/2000','Green','Red','White','','','','','','','',''), 
('jkl','5/1/2001','Black','Yellow','Silver','10/10/2001','Green','Black','Silver','','','',''); 

select * from #myInput 
select * from #myOUTPUT 

我们正在寻找17个或更少的订单。至少在目前的时刻，我们没有超过一打订单的任何一个客户。

我试过了几个不同的东西 - 枢轴似乎并没有产生我期待的输出。我想可能是dense_rank来决定首先需要多少列，然后通过cte插入到光标句柄中？但我无法获得所需的输出。请注意，源“日期”字段以varchar形式存储在数据库中。此外，没有订单号码 - 所以不公平仅仅来自客户ID和日期。

Answer 1

我会使用条件聚合来解决这个问题。如果我理解正确：

select customer, 
     max(case when seqnum_co = 1 then orderdate end) as orderdate_1, 
     max(case when seqnum_co = 1 and item = 'Part_A' then itemcolor end) as parta_1, 
     max(case when seqnum_co = 1 and item = 'Part_B' then itemcolor end) as partb_1, 
     max(case when seqnum_co = 1 and item = 'Part_C' then itemcolor end) as partc_1, 
     max(case when seqnum_co = 2 then orderdate end) as orderdate_2, 
     max(case when seqnum_co = 2 and item = 'Part_A' then itemcolor end) as parta_2, 
     max(case when seqnum_co = 2 and item = 'Part_B' then itemcolor end) as partb_2, 
     max(case when seqnum_co = 2 and item = 'Part_C' then itemcolor end) as partc_2, 
     . . . 
from (select i.*, 
      dense_rank() over (partition by i.customerid order by orderdate) as seqnum_co 
     from #myinput 
    ) i 
group by customer;