Thought this would be easy but I am having trouble. I need to pivot (or unpivot) a table (called da_LMP_downloads) with 100,000+ rows. I'm starting with this: table_before
Before:
Market Day | Node| Node_Type | LMP_Value| HE1 | HE2| HE 3|.......|HE24|
10/1/12 |AEC| Interface | LMP | 17.82 | 16.44 | 16.45
10/1/12 |AEC| Interface | MCC | .14 | .16 | .62
10/1/12 |AEC| Interface | MLC | -.38 | -.34 | .34
But need it to look like this after the pivot: table_after
After:
Market_Day | Node | HE | daLMP | daMCC | daMLC
10/1/12 | AEC | 1 | 17.82 | .14 | -.38
10/1/12 | AEC | 2| 16.44| .16 | -.34
10/1/12 | AEC | 3 | 16.45 | .62 | .34
IF your dbms is SQL Server then you can use CROSS APPLY with VALUES to achieve the "unpivot" portion, but you also need a small "pivot" operation as well it appears and for that a GROUP BY will work. Note this is ONLY applicable to SQL Server, other databases have very different syntax for unpivot/pivot.
/* input looks like this...
Market_Day Node Node_Type LMP_Value HE1 HE2 .... HE24
2012-10-01 AEC Interface LMP 17.82 16.44 .... 19.77
*/
SELECT
sq.Market_Day
, sq.Node
, sq.Node_Type
, sq.HE
, MAX(sq.daLMP) AS daLMP
, MAX(sq.daMCC) AS daMCC
, MAX(sq.daMLC) AS daMLC
FROM (
SELECT
t.Market_Day
, t.Node
, t.Node_Type
, ca.HE
, CASE WHEN LMP_Value = 'LMP' THEN ca.val END AS daLMP
, CASE WHEN LMP_Value = 'MCC' THEN ca.val END AS daMCC
, CASE WHEN LMP_Value = 'MLC' THEN ca.val END AS daMLC
FROM Table1 t
CROSS APPLY (
VALUES
(1, HE1)
, (2, HE2)
, (3, HE3)
, (4, HE4)
, (5, HE5)
, (6, HE6)
, (7, HE7)
, (8, HE8)
, (9, HE9)
, (10, HE10)
, (11, HE11)
, (12, HE12)
, (13, HE13)
, (14, HE14)
, (15, HE15)
, (16, HE16)
, (17, HE17)
, (18, HE18)
, (19, HE19)
, (20, HE20)
, (21, HE21)
, (22, HE22)
, (23, HE23)
, (24, HE24)
) AS ca (HE, val)
) AS sq
GROUP BY
sq.Market_Day
, sq.Node
, sq.Node_Type
, sq.HE
Note TSQL actually does have both "pivot" and "unpivot" operators but I find using cross apply/values to be as efficient and simpler to implement.