October 8, 2008 at 4:17 am
Hello friends,
I know i can do this using pivot very easily in 2005. But we are working in 2000 and next month we are getting 2005. But till then i need to find some way to work around.
I have one table as below.
Total Name Month
1 rich Apr-08
354 jo Apr-08
2 stu Apr-08
1 john Apr-08
5 rich May-08
5 jo May-08
1 stu May-08
90 john May-08
98 rich Jun-08
115 jo Jun-08
1 stu Jun-08
98 john Jun-08
I need output like below:
Name Apr-08 May-08 Jun-08
Rich 1 5 98
jo 354 5 115
stu 2 1 1
john 1 90 98
Thanks.
October 8, 2008 at 4:29 am
You can do this by using sum, group by and case:
SELECT [Name]
, SUM(CASE WHEN [Month] = 'Apr-08' THEN [Total]
END) AS [Apr-08]
, SUM(CASE WHEN [Month] = 'May-08' THEN [Total]
END) AS [May-08]
, SUM(CASE WHEN [Month] = 'Jun-08' THEN [Total]
END) AS [Jun-08]
FROM TableBelow
GROUP BY [Name]
Regards,
Andras
October 8, 2008 at 4:40 am
Hi,
I wrote the exact code. I dont believe it is so easy. Thank you very much for your reply.
Thanks,
Vijay
October 8, 2008 at 4:53 am
Again i am thinking about year 2008. We will get more and more data as time goes and in this way it will work only for certain months and year.
What about 2009?
October 8, 2008 at 5:11 am
dva2007 (10/8/2008)
Again i am thinking about year 2008. We will get more and more data as time goes and in this way it will work only for certain months and year.What about 2009?
You could make this query to be based on dynamic SQL. For dynamic PIVOTS see http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx. The same or similar approach could be used to create the above SQL statement. It can be encapsulated in a stored procedure too.
Regards,
Andras
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply