Thursday, March 31, 2011

How to UNPIVOT to split columns into rows?

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.

From stackoverflow
  • 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

  • Try

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

0 comments:

Post a Comment