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

This page was last updated on May 01, 2006 04:28 PM.