How to UNPIVOT to split columns into rows?

Question

I've got a sql query (using MS-SQL 2005) that generates data with these columns:

TimeStamp | SpeedMax | SpeedMin | HeightMax | HeightMin
-------------------------------------------------------
10        | 50       | 10       | 300       | 70

The form I need it in though is this:

TimeStamp | Speed | Height
---------------------------
10        | 50    | 300          <-- one row for the max values 
10        | 10    | 70           <-- a second row for the min values

Given the first result set... what query would I need to get the data into the second format? I think it might involve an unpivot, but I'm new to that, and am having trouble working out what to write.

Thank you very much.

Answer 1

SELECT TimeStamp,
       minmax,
       CASE WHEN minmax = 0 THEN SpeedMax ELSE SpeedMin END AS Speed
       CASE WHEN minmax = 0 THEN HeightMax ELSE HeightMin END AS Height
FROM Table,
(
    SELECT 0 AS minmax
    UNION ALL
    SELECT 1
) mm

You may add

OPTION (FORCE ORDER)

to avoid double scan on Table

Answer 2

Try

SELECT TimeStamp, SpeedMax AS Speed, HeightMax AS Height
FROM Table
UNION ALL
SELECT TimeStamp, SpeedMin AS Speed, HeightMin AS Height
FROM Table