在T中枢轴两列

Question

首先，我已经看了两天的例子，并尝试应用，但没有成功。我不理解枢轴的工作原理，我希望得到一些帮助。

我有一个数据集，每个客户有多行--每次购买一行。我想得到每一个客户一行-与多达6次购买和购买日期为每个。

老实说，我甚至不知道这是否可能.由于购买日期，PDate可能有如此广泛的变化。？

下面是我的起始数据集的SQL：

DECLARE @Test AS TABLE 
(
    Location    VARCHAR(20),
    Mgr     VARCHAR(30),        
    CId     VARCHAR(20),
    CName       VARCHAR(100),
    BDate       DATE,
    Age     Int,
    Item        Varchar(15),
    PDate       Date
)

Insert Into @Test 
(Location, Mgr, CId, CName, BDate, Age, Item, PDate) 
Values
('A','Bob','1','Bill Jones','1967-04-27', 50,'Hammer','2017-04-05'),
('A','Bob','1','Bill Jones','1967-04-27', 50,'Nails','2017-03-17'),
('A','Bob','1','Bill Jones','1967-04-27', 50,'Screws','2017-02-15'),
('A','Bob','1','Bill Jones','1967-04-27', 50,'Nails','2017-01-26'),
('A','Bob','1','Bill Jones','1967-04-27', 50,'Screws','2016-12-20'),
('A','Bob','1','Bill Jones','1967-04-27', 50,'Nails','2016-11-03'),
('B','Dan','15','Sharon Jones','1969-04-27', 48,'Nails','2017-04-05'),
('B','Dan','15','Sharon Jones','1969-04-27', 48,'Nails','2017-03-07'),
('B','Dan','15','Sharon Jones','1969-04-27', 48,'Screws','2017-02-18')
Select * From @Test

我要看看这个：

A Bob 1  Bill Jones   1967-04-27 50 Hammer 2017-04-05 Nails 2017-03-17 .... 
B Dan 15 Sharon Jones 1969-04-27 48 Nails  2017-04-05 Nails 2017-03-07 .... 

..。从本质上说，每一个CId都有一行: Location，Mgr，CId，CName，BDate，Age，Item1，Date1，Item2，Date2，Item3，Date3 .多达6件购买的物品。

提前感谢！

Answer 1

因为您不需要在6的时候进行动态化和最大化，一个简单的条件聚合就可以了。

Select Location, Mgr, CId, CName, BDate, Age
      ,[Item1] = max(case when RN=1 then Item end)
      ,[Date1] = max(case when RN=1 then Pdate end)
      ,[Item2] = max(case when RN=2 then Item end)
      ,[Date2] = max(case when RN=2 then Pdate end)
      ,[Item3] = max(case when RN=3 then Item end)
      ,[Date3] = max(case when RN=3 then Pdate end)
      ,[Item4] = max(case when RN=4 then Item end)
      ,[Date4] = max(case when RN=4 then Pdate end)
      ,[Item5] = max(case when RN=5 then Item end)
      ,[Date5] = max(case when RN=5 then Pdate end)
      ,[Item6] = max(case when RN=6 then Item end)
      ,[Date6] = max(case when RN=6 then Pdate end)
 From (
        Select *
            ,RN = Row_Number() over (Partition By Location, Mgr, CId, CName, BDate, Age Order by Item,PDate)
        From Test
      ) A
 Group By Location, Mgr, CId, CName, BDate, Age

返回

​

应请求-一些评论

这是一个简单的条件聚合，稍微有点扭曲。扭转是使用窗口函数Row_Number()的子查询。子查询生成以下内容：

​

​

注意最后一列RN。您可能会看到，它是增量式的，但由Location, Mgr, CId, CName, BDate, Age分区，由Item,PDate命令。

一旦子查询被重新处理(使用RN)，我们就可以应用最终的aggretation，它本质上是一个枢轴。

窗口功能可以是无价的，值得你花时间去适应它们，

Answer 2

我到处玩，这就是我想出来的。但约翰的效率更高。

WITH preSelect AS
(Select ROW_NUMBER() OVER(PARTITION BY Location,Mgr,CId,CName,BDate,Age 
ORDER BY LOCATION) 'rowNum',
     Location,Mgr,CId,CName,BDate,Age,Item,PDate 
From @Test)

Select t.Location,t.Mgr,t.CId,t.CName,t.BDate,t.Age,
    t1.Item 'Item 1',t1.PDate 'Date 1',
    t2.Item 'Item 2',t2.PDate 'Date 2',
    t3.Item 'Item 3',t3.PDate 'Date 3',
    t4.Item 'Item 4',t4.PDate 'Date 4',
    t5.Item 'Item 5',t5.PDate 'Date 5',
    t6.Item 'Item 6',t6.PDate 'Date 6'

From @Test t
 LEFT JOIN preSelect t1 ON t.Location = t1.Location AND t1.rowNum = 1
 LEFT JOIN preSelect t2 ON t.Location = t2.Location AND t2.rowNum = 2
 LEFT JOIN preSelect t3 ON t.Location = t3.Location AND t3.rowNum = 3
 LEFT JOIN preSelect t4 ON t.Location = t4.Location AND t4.rowNum = 4
 LEFT JOIN preSelect t5 ON t.Location = t5.Location AND t5.rowNum = 5
 LEFT JOIN preSelect t6 ON t.Location = t6.Location AND t6.rowNum = 6
GROUP BY t.Location,t.Mgr,t.CId,t.CName,t.BDate,t.Age,
    t1.Item,t1.PDate,t2.Item,t2.PDate,t3.Item,t3.PDate,t4.Item,t4.PDate,t5.Item,t5.PDate,t6.Item,t6.PDate