Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kowalsky
Starting Member
29 Posts |
 Posted - 2011-01-31 : 16:50:45
|
| hi all,I am trying to get a result set from a table with transposed rows.The table looks like this:LOCATION_ID MEASUREMENT_DATE MEASUREMENT_VALUE101 2010-12-01 1234.00101 2010-12-02 1234.00....101 2010-12-31 1357.00102 2010-12-01 1468.00102 2010-12-02 1479.00....102 2010-12-31 1555.00I am trying to get the result in the following format:MEASUREMENT_DATE MEASUREMENT_VALUE4LOC1 MEASUREMENT_VALUE4LOC2 ...2010-12-01 1234.00 1468.002010-12-02 1234.00 1479.00....2010-12-31 1357.00 1555.00I have around 30 locations; is there a nicer way than have 30 self joins on my MEASUREMENTS table?Thanks a bunch,kowalskykowalsky |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-31 : 17:02:28
|
Perhaps PIVOT, using LOCATION as your column and the date as your row with SUM(VALUE) (provided there is only 1 date per location, the SUM(Value) should be equivalent to the results you want.less work than 30 self joins..you just have to specify column names for each possible locationsomething along these lines..SELECT Measurement_Date, '101' as Location101, '102' as Location102,--etcFROM (SELECT LOCATION_ID, MEASUREMENT_DATE ,MEASUREMENT_VALUE) pPIVOT(SUM (MEASUREMENT_VALUE)FOR Location_ID) AS pvtORDER BY Measurement_Date;
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
query for transposing