How to write this SQL
-
Tuesday, May 08, 2012 7:45 AM
Hi All,
I have one table, which looks like this:
Name, WeekName, Revenue
A, Week1,100
A,Week2,200
B,Week1,10
B,Week2,10
For Now I just want to show them like this:
Name Week1, Week2
A, 100, 200
B,10,10
How to achieve it?
And I also want to make the week name as parameter, I mean how many week will be shown is dynamic.
Thanks.
All Replies
-
Tuesday, May 08, 2012 7:50 AMAnswerer
SELECT Name,MAX(CASE WHEN WeekName ='Week1' THEN Revenue END) Week1,
MAX(CASE WHEN WeekName ='Week2' THEN Revenue END) Week2,
.................................
FROM tbl GROUP BY Name
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- Proposed As Answer by K A R T H I S Tuesday, May 08, 2012 8:56 AM
-
Tuesday, May 08, 2012 9:01 AMModerator
Hello BIMaster,
What you want is using pivot table in SQL Server
You can check the reference for a sample case, you can also try the following Select
select * from ( select Name, WeekName, Revenue from WeeklyRevenue ) d PIVOT ( SUM(Revenue) FOR WeekName IN ( [Week1],[Week2] )) pIf you also want the column names Week1, Week2, etc to be automatically assigned, then you can build this pivot query as a dynamic pivot query- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, May 08, 2012 5:29 PM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Sunday, May 13, 2012 7:47 AM
-
Tuesday, May 08, 2012 9:03 AM
Refer to the below link on creating dynamic pivot query
.png)
