Pivot two columns in T-SQL

Question

First of all - I have been looking at examples for two days and have tried applying them but have not been successful. I do not understand the mechanics of how Pivot works and I would appreciate some help.

I have a dataset with multiple rows per Customer - one row per purchase. I want to get one row per customer - with up to 6 purchases and the purchase date for each.

Honestly, I don't even know if this is possible ... as the Purchase Dates [PDate] can vary so widely. ?

Here is SQL for my starting Dataset:

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

I need to see this:

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 .... 

... essentially, one row for each CId with: Location, Mgr, CId, CName, BDate, Age, Item1, Date1, Item2, Date2, Item3, Date3 ... up to 6 purchased items.

Thanks in advance!

Answer 1

Since you don't need to go dynamic and maxing out at 6, a simple conditional aggregation would do.

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

Returns

As Requested - Some Commentary

This is a simple conditional aggregation with a little twist. The twist is the sub-query using a window function Row_Number(). The sub-query generates the following:

Notice the last column RN. You may see that it is incremental YET partitioned by Location, Mgr, CId, CName, BDate, Age and ordered by Item,PDate

Once the sub-query is resoved (with the RN), we then can apply the final aggretation which is essentially a pivot

The window function can be invaluable and well worth your time to get comfortable with them,