```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
```