Binaryworld - This is an example that shows how to do the opposite of pivot. ... [ SQL Server -> T-SQL ]

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