SQL Server - Help with SQL Query.

Asked By Charvi on 08-Mar-12 09:55 AM
Earn up to 10 extra points for answering this tough question.
Hi,


I have a report that currently looks like this.


Week 1 Week 2                                         Week 3
Region Name   NAV Today  NAV Yest  Mkt Rate            NAV Today    NAV Yest Mkt Rate   NAV Today  NAV Yest Mkt Rate
Asia 126                       128               7.2                                 131       130     8.2                                140     141      9.2    
Emea               128            129               7.2                                 132       133     8.2                                  142     143      9.2        
Americas        129                       130              7.2                                 134       135     8.2                                  144     145      9.2
Now, our client needs the report in the format below :




Week 1               Week 2 Week 3
Region Name      Parameter   Value            Parameter   Value              Parameter   Value
Asia            NAV Today      126                     NAV Today  131    NAV Today    
Asia            NAV Yest         128            NAV Yest 130                     NAV Yest
Asia            Mkt Rate           7.2                      Mkt Rate   8.2                         Mkt Rate
Emea     NAV Today     128            NAV Today               NAV Today
Emea     NAV Yest         129                      NAV Yest                             NAV Yest
Emea     Mkt Rate           7.2                      Mkt Rate                                 Mkt Rate
Americas   NAV Today     129                      NAV Today             NAV Today
Americas   NAV Yest         130                      NAV Yest             NAV Yest  
Americas   Mkt Rate           7.2                       Mkt Rate                                 Mkt Rate




Any help would be greatly appreciated.


Regards,
Charvi.
Pat Hartman replied to Charvi on 08-Mar-12 12:49 PM
You've shown us what the report looks like but we need to know what the table looks like.  If the table is properly normalized, you will need to use PIVOT to denormalize it into the report.
Sandeep Mittal replied to Charvi on 09-Mar-12 02:52 AM
Post your table structure with sample data.
I believe pivot or unpivot would work for you depending on your table structure
Parag Satpute replied to Charvi on 09-Mar-12 03:18 PM
You can use the below SQL Query for getting the output as you want (Week 1 output format),

SELECT Region Name, NAV Today, NAV Yest, Mkt Rate
FROM 
   (SELECT Region Name, NAV Today, NAV Yest FROM TableName) p
UNPIVOT
   (Orders FOR NAV Today, NAV Yesterday IN 
      (NAV Today, NAV Yest)) AS unpvt;
GO


Same way you can use the above query and modify it to get the output for Week 2 and Week 3.