CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Talay
Talay

Reputation: 381

Pivot two columns in T-SQL

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!

Upvotes: 3

Views: 285

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Returnsenter image description here

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:

enter image description here

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,

Upvotes: 1

Jason
Jason

Reputation: 945

I played around and this is what I came up with. John's is more efficient though. 

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

Upvotes: 0

Related Questions