Binaryworld - Changing columns to rows. ... [ SQL Server -> T-SQL ]

Another example that shows how to convert columnar values into individual rows.

CREATE TABLE #Classes (
Name  varchar( 30 ) ,
Class1  varchar( 30 ) ,
Class2  varchar( 30 ) ,
Class3 varchar( 30 )
);

INSERT INTO #Classes VALUES( 'Steve' , 'Math' , 'Calc' , 'Calc2' );
INSERT INTO #Classes VALUES( 'Peter' , 'English' , NULL , 'Chinese' );
SELECT * FROM #Classes;
/*
Name Class1 Class2 Class3
------ -------- ------- ------------------------------
Steve Math   Calc  Calc2
Peter English NULL  Chinese
*/
GO

-- Objective:
-- To use the denormalized table and
-- get the classes as rows for each student.
SELECT *
FROM (
SELECT Name , CASE ClassNo
        WHEN 1 THEN Class1
        WHEN 2 THEN Class2
        WHEN 3 THEN Class3
       END AS Classes
FROM #Classes
CROSS JOIN
(
SELECT 1 AS ClassNo UNION ALL SELECT 2 UNION ALL SELECT 3) AS n
) AS c
WHERE classes IS NOT NULL;
/*
Name              Classes
------------------------------ ------------------------------
Steve             Math
Steve             Calc
Steve             Calc2
Peter             English
Peter             Chinese
*/
-- or a UNION ALL query. This is slower in MS SQL Server b'cos
-- the optimizer doesn't handle the same table specified in the
-- different SELECT statements efficiently.
SELECT Name , Class1 AS Classes FROM #Classes WHERE Class1 IS NOT NULL
UNION ALL
SELECT Name , Class2 FROM #Classes WHERE Class2 IS NOT NULL
UNION ALL
SELECT Name , Class3 FROM #Classes WHERE Class3 IS NOT NULL;
GO