I have an Excel spreadsheet that imports into a table like so:
+-------------------------------------------------------------------------+
| Col1 Col2 Col3 Col4 Col5 |
+-------------------------------------------------------------------------+
| Ref Name 01-01-2013 02-01-2013 03-01-2013 |
| 1 John 500 550 600 |
| 2 Fred 600 650 400 |
| 3 Paul 700 750 550 |
| 4 Steve 800 850 700 |
+-------------------------------------------------------------------------+
My goal is to change it to look like this:
+-------------------------------------------------------------------------+
| Ref Name Date Sales |
+-------------------------------------------------------------------------+
| 1 John 01-01-2013 500 |
| 1 John 02-02-2013 550 |
| 1 John 03-01-2013 600 |
| 2 Fred 01-01-2013 600 |
| ..... |
+-------------------------------------------------------------------------+
So far I figured out how to use UNPIVOT to get the dates and sales numbers into 1 column but that doesn't solve the problem of breaking the dates out into their own column. Any help is appreciated. Thanks!!
You could possibly use two separate UNPIVOT queries and then join them. The first unpivot, will convert the row with the ref value in col1, then the second subquery does an unpivot of the sales. You join the subqueries on the previous column names:
select s.col1,
s.col2,
d.value date,
s.value sales
from
(
select col1, col2, col, value
from yt
unpivot
(
value
for col in (col3, col4, col5)
) un
where col1 = 'ref'
) d
inner join
(
select col1, col2, col, value
from yt
unpivot
(
value
for col in (col3, col4, col5)
) un
where col1 <> 'ref'
) s
on d.col = s.col;