Unpivoting Data
Posted by Jeff Rix on July 27, 2011 under T-SQL | 
Be the First to Comment
–Original Table
SELECT *
FROM TCT_Legacy_Unformatted
FROM TCT_Legacy_Unformatted
UNPIVOT Original table takes the column names into Indicator ID and the values of the columns into scores
INSERT INTO TCT_LEGACY_FORMATTED
SELECT ESP_ID, SSN, School_Year, Building, order_indicator, score, NULL, NULL
FROM TCT_Legacy_Unformatted
UNPIVOT(score FOR order_indicator IN([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18],
[19], [20], [21], [22], [23], [24], [25], [26], [27], [28])) AS U;
–delete where the score is null
delete from TCT_Legacy_Formatted
where score is null
where score is null
–Formatted table with results of the UNPIVOT
SELECT *
FROM TCT_Legacy_Formatted
FROM TCT_Legacy_Formatted
