SQL Server advanced Pivot grouped rows to columns

Question

I need to Pivot/rotate data in rows into columns - but a little different from most examples I've seen.

We have customers that will buy things in sets (think a pizza ingredient seller... people will always buy cheese, dough, and sauce; optionally some will buy toppings, but we don't care about that).

What I need to do is sort this row data, by order date into columns. Below are two scripts to fill temp input and temp output table to show what I'm trying to achieve.

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')
;

And the result:

insert into #myOUTPUT 

(CustomerID,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

We're looking for 17 or less orders. At least at the current moment, we don't have more than 1 dozen orders for any one customer.

I've tried a couple of different things- pivot doesn't seem to produce the output i'm looking for. I was thinking perhaps dense_rank to determine how many columns we'll need at first, and then insert into a cursor handle via cte? But i'm unable to get exactly the output needed. Note that the source "date" field is stored in DB as varchar. Also, there's no order number - so uninqueness is only from customer id, and date.

Answer 1

I would approach this using conditional aggregation. If I understand correctly:

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;