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 ) mmYou 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