Pivot with multiple colums and rows

Question

Could you help me to achieve the following

I have this SQL output table

DateWeek Keep_1 This_1 Order_1 Keep_2 This_2 Order_2 Keep_1-Keep_2 This_1-This_2 Order_1-Order_2
1/1/2013 9      8      7       6       5     4       3             3             3

and turn it into

RowOrder Column_1 Column_2 Column_1-Column_2
Keep     9        6        3
This     8        5        3
Order    7        4        3

As you see I have to keep the order in the rows, so I can not order alphabetically. Also I have to stack the Keep_1 This_1 Order_1 together and Keep_2 This_2 Order_2 also together and operate Column_1 with Column_2

Any ideas how to achieve this?

Thanks

score 1 · Accepted Answer · answered Mar 13 '13 at 17:25

If you are using SQL Server 2008+, then you can use CROSS APPLY and VALUES:

select c.roworder,
  c.col1,
  c.col2,
  c.col3
from yourtable t
cross apply
(
  values 
    ('Keep', Keep_1, Keep_2, Keep_1_Keep_2),
    ('This', This_1, This_2, This_1_This_2),
    ('Order', Order_1, Order_2, Order_1_Order_2)
) c (roworder, col1, col2, col3)

See SQL Fiddle with Demo.

This can also be done using a UNION ALL query in any database:

select 'Keep' RowOrder, 
  Keep_1 col1, 
  Keep_2 col2, 
  Keep_1_Keep_2 col3
from yourtable
union all
select 'This' RowOrder, 
  This_1 col1, 
  This_2 col2, 
  This_1_This_2 col3
from yourtable
union all
select 'Order' RowOrder, 
  Order_1 col1, 
  Order_2 col2, 
  Order_1_Order_2 col3
from yourtable

See SQL Fiddle with Demo

Pivot with multiple colums and rows

1 Answers1