sql - Pivot Assistance Required -

i have table . table 1.

customer    product     value   quantity    order number   dave    product 1   15  1   154 dave    product 2   25  5   154 dave    product 3   45  4   15 rob     product 2   222 33  233 

now want , table 2.

customer    product 1 quantity  product 1 value     price per item ( value /quantity) product 1     product 2 quantity  product 2 value     price per item ( value /quantity) product 2     product 3 quantity  product 3 value     price per item ( value /quantity) product 3 order number   dave    1   15  15  5   25  5   null    null    null    154  dave    null    null    null    null    null    null    4   45  11.25   15  rob     null    null    null    33  222 6.727272727 null    null    null    233 

i thinking pivot wasn't sure how construct . number of products not fixed , change in table 1.

in order result, advise applying both unpivot , pivot data.

the unpivot convert column data table rows. once data unpivoted, can apply pivot.

since using sql server 2008+ can use cross apply values clause unpivot. prior 2008, use unpivot function. code unpivot data is:

select t.customer,   replace(t.product, ' ', '')+'_'+c.col piv_col,    c.val,   t.ordernumber table1 t cross apply (   values     ('value', cast(value varchar(10))),     ('quantity', cast(quantity varchar(10))),     ('priceperunit', cast((value/quantity) *1.0 varchar(10))) ) c (col, val); 

see demo. converts data following format:

| customer |               piv_col |  val | ordernumber | --------------------------------------------------------- |     dave |        product1_value |   15 |         154 | |     dave |     product1_quantity |    1 |         154 | |     dave | product1_priceperunit | 15.0 |         154 | |     dave |        product2_value |   25 |         154 | 

you can see row dave order 154 has been turned rows , have created new column names used pivot (piv_col). column has concatenated product name of previous column headers (value, quantity).

since data in single row, can apply pivot function data. final code be:

select customer,   product1_quantity, product1_value, product1_priceperunit,   product2_quantity, product2_value, product2_priceperunit,   product3_quantity, product3_value, product3_priceperunit,   ordernumber (   select t.customer,     replace(t.product, ' ', '')+'_'+c.col piv_col,      c.val,     t.ordernumber   table1 t   cross apply   (     values       ('value', cast(value varchar(10))),       ('quantity', cast(quantity varchar(10))),       ('priceperunit', cast((value/quantity) *1.0 varchar(10)))   ) c (col, val) ) d pivot (   max(val)   piv_col in(product1_quantity, product1_value, product1_priceperunit,                  product2_quantity, product2_value, product2_priceperunit,                  product3_quantity, product3_value, product3_priceperunit) ) piv; 

see sql fiddle demo.

the above works great if have known number of products, if not, need use dynamic sql.

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select  ',' + quotename(replace(t.product, ' ', '')+'_'+c.col)                      table1 t                     cross apply                     (                       values ('value', 1), ('quantity', 0),('priceperunit', 3)                     ) c (col, so)                         group product, col,                     order product,             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select customer, ' + @cols + ', ordernumber                             (                 select t.customer,                   replace(t.product, '' '', '''')+''_''+c.col piv_col,                    c.val,                   t.ordernumber                 table1 t                 cross apply                 (                   values                     (''value'', cast(value varchar(10))),                     (''quantity'', cast(quantity varchar(10))),                     (''priceperunit'', cast((value/quantity) *1.0 varchar(10)))                 ) c (col, val)               ) d               pivot               (                 max(val)                 piv_col in (' + @cols + ')               ) p '  execute(@query); 

see sql fiddle demo. these queries give result:

| customer | product1_quantity | product1_value | product1_priceperunit | product2_quantity | product2_value | product2_priceperunit | product3_quantity | product3_value | product3_priceperunit | ordernumber | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |     dave |            (null) |         (null) |                (null) |            (null) |         (null) |                (null) |                 4 |             45 |                  11.0 |          15 | |     dave |                 1 |             15 |                  15.0 |                 5 |             25 |                   5.0 |            (null) |         (null) |                (null) |         154 | |      rob |            (null) |         (null) |                (null) |                33 |            222 |                   6.0 |            (null) |         (null) |                (null) |         233 |