CREATE TABLE #e (
Line1 int, Line2 int, Line3 int, Line4 int, Line5 int
)
INSERT INTO #e VALUES ( 1, 2, 3, 4, 5 );
INSERT INTO #e VALUES ( 11, 22, 33, 44, 55 );
SELECT * FROM #e;
-- Output:
/*
Line1 Line2 Line3 Line4 Line5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
11 22 33 44 55
*/
GO
-- Objective:
/*
To convert the 5 columns from the denormalized table to rows.
*/
-- The efficient one first
SELECT number AS line , CASE number
WHEN 1 THEN line1
WHEN 2 THEN line2
WHEN 3 THEN line3
WHEN 4 THEN line4
WHEN 5 THEN line5
END AS value
FROM #e JOIN
(
-- This table is also discussed elsewhere in the SQL6x-70 Scripts page
-- It contains just numbers from 1 to X.
SELECT number FROM Numbers WHERE number Between 1 And 5
) AS n
ON 1 = 1
ORDER BY line, value;
/*
line value
------ -----------
1 1
1 11
2 2
2 22
3 3
3 33
4 4
4 44
5 5
5 55
*/
-- Using UNION ALL query
SELECT 1 AS line, line1 AS value FROM #e
UNION ALL
SELECT 2, line2 FROM #e
UNION ALL
SELECT 3, line3 FROM #e
UNION ALL
SELECT 4, line4 FROM #e
UNION ALL
SELECT 5, line5 FROM #e;
/*
Slightly convoluted one but works all the same. This will be
helpful if you are doing other summaries with the rows.
See BOL help on CUBE & ROLLUP for more details.
*/
SELECT
CASE
WHEN (GROUPING(line1) = 0 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and
GROUPING(line4) = 1 and GROUPING(line5) = 1) THEN 1
WHEN (GROUPING(line1) = 1 and GROUPING(line2) = 0 and GROUPING(line3) = 1 and
GROUPING(line4) = 1 and GROUPING(line5) = 1) THEN 2
WHEN (GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 0 and
GROUPING(line4) = 1 and GROUPING(line5) = 1) THEN 3
WHEN (GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and
GROUPING(line4) = 0 and GROUPING(line5) = 1) THEN 4
WHEN (GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and
GROUPING(line4) = 1 and GROUPING(line5) = 0) THEN 5
END AS Line,
COALESCE( line1, line2, line3, line4, line5 ) AS value
FROM #e
GROUP BY line1, line2, line3, line4, line5
WITH CUBE
HAVING
(GROUPING(line1) = 0 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and GROUPING(line4) = 1 and GROUPING(line5) = 1) or
(GROUPING(line1) = 1 and GROUPING(line2) = 0 and GROUPING(line3) = 1 and GROUPING(line4) = 1 and GROUPING(line5) = 1) or
(GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 0 and GROUPING(line4) = 1 and GROUPING(line5) = 1) or
(GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and GROUPING(line4) = 0 and GROUPING(line5) = 1) or
(GROUPING(line1) = 1 and GROUPING(line2) = 1 and GROUPING(line3) = 1 and GROUPING(line4) = 1 and GROUPING(line5) = 0);
GO
This page was last updated on May 01, 2006 04:28 PM.