Converting Table Columns to Rows

Question

I have a SQL Server table where I would like to convert columns to rows (wide to tall).

The current table format:

Desired table format:

I have looked into a method call pivoting/unpivoting but I can't seem to get the formatting just the way I need it. Thank you for any help!

Possible duplicate of [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) — Sergey Kalinichenko, Apr 27 '18 at 16:19
You mean an actual table, or do you mean you want a result set? It is a really bad idea to store a date and then a cost both in the same column — dfundako, Apr 27 '18 at 16:19
Sorry for the confusion, I will need to insert this result set into a new table. — zenzendaro, Apr 27 '18 at 16:22
What exactly is off with the formatting that isn't just the way you need it? — DrHouseofSQL, Apr 27 '18 at 16:24

John Cappelletti · Answer 1 · 2018-04-27T16:57:29.240

UnPivot is certainly more performant, but here is one approach which will dynamically unpivot virtually any table, query, or record WITHOUT actually using Dynamic SQL

Example

Declare @YourTable Table (Date date,cost1 int,cost2 int,cost3 int)
Insert Into @YourTable Values 
 ('6/30/2017',3,4,5),
 ('6/24/2017',3,4,5),
 ('6/22/2017',3,4,5)

Select C.*
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Column1','Column2')
             ) C

Returns

Item    Value
Date    2017-06-30
cost1   3
cost2   4
cost3   5
Date    2017-06-24
cost1   3
cost2   4
cost3   5
Date    2017-06-22
cost1   3
cost2   4
cost3   5

Now, with a little twist

Select A.Date
      ,C.*
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Date','Column2')
             ) C

Returns

Date        Item    Value
2017-06-30  cost1   3
2017-06-30  cost2   4
2017-06-30  cost3   5
2017-06-24  cost1   3
2017-06-24  cost2   4
2017-06-24  cost3   5
2017-06-22  cost1   3
2017-06-22  cost2   4
2017-06-22  cost3   5

Converting Table Columns to Rows

1 Answers1