Rahul Sah Rahul Sah - 3 months ago 8
SQL Question

SQL query for putting the row information for various regions in the column of same result set row.

I am having table in the following as shown below:
It is having daily whether information for various region.

Daily Weather Information
WForecastDate | TimeStamp | RegionId | Temp | WindSpeed | Humidity
----------------------------------------------------------------------------
2016-08-16 | 1 | 100 | 23 | 123 |23
2016-08-16 | 2 | 100 | 24 | 123 |24
2016-08-16 | 3 | 100 | 24 | 123 |22
2016-08-16 | 4 | 100 | 23 | 123 |21
2016-08-16 | 5 | 100 | 25 | 123 |27
2016-08-16 | 6 | 100 | 24 | 123 |26
2016-08-16 | 7 | 100 | 22 | 123 |27
2016-08-16 | 8 | 100 | 21 | 123 |26
2016-08-16 | 9 | 100 | 20 | 123 |23
2016-08-16 | 1 | 101 | 23 | 123 |22
2016-08-16 | 2 | 101 | 22 | 123 |21
2016-08-16 | 3 | 101 | 21 | 123 |27
2016-08-16 | 4 | 101 | 22 | 123 |25
2016-08-16 | 5 | 101 | 21 | 123 |23
2016-08-16 | 6 | 101 | 21 | 123 |24
2016-08-16 | 1 | 102 | 23 | 123 |22
2016-08-16 | 2 | 102 | 22 | 123 |21
2016-08-16 | 3 | 102 | 21 | 123 |27
2016-08-16 | 4 | 102 | 22 | 123 |25
2016-08-16 | 5 | 102 | 21 | 123 |23
2016-08-16 | 6 | 102 | 21 | 123 |24
.............................................................
.............................................................


My task is to get the output result set as the below shown format, which will have temp,windspeed and humidity information for various regions on the same row based on criteria of there WForecastDate and timeStamp. Here Temp100,Temp101 etc are the temperature for this regions.

Output Result Set
WForecastDate | TimeStamp | Temp100 | Temp101 | Temp102 | Humidity100 | Humidity101 | Humidity102 | Humidity103 | WindSpeed100 | WindSpeed101 | WindSpeed102
----------------------------------------------------------------------------


Please help and tell me which query should be written so to achieve this task.

Answer

One method in SQL is to use conditional aggregation:

select wforecastdate, timestamp, 
       max(case when regionid = 100 then temp end) as temp_100,
       max(case when regionid = 101 then temp end) as temp_101,
       max(case when regionid = 102 then temp end) as temp_102,
       max(case when regionid = 100 then humidity end) as humidity_100,
       max(case when regionid = 101 then humidity end) as humidity_101,
       max(case when regionid = 102 then humidity end) as humidity_102,
       max(case when regionid = 100 then windspeed end) as windspeed_100,
       max(case when regionid = 101 then windspeed end) as windspeed_101,
       max(case when regionid = 102 then windspeed end) as windspeed_102
from dailyweatherinformation dwi
group by wforecastdate, timestamp
order by wforecastdate