By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Change "Group By" data in one column into multiple columns based on Group

P: 28
Hi,

I have a SQL table that has data like this:

Title Month Info
1 ---- 7 ---- 100
1 ---- 7 ---- 100
1 ---- 8 ---- 200
1 ---- 8 ---- 250
2 ---- 7 ---- 150
2 ---- 7 ---- 150
2 ---- 8 ---- 300
2 ---- 8 ---- 300
3 ---- 7 ---- 250
3 ---- 7 ---- 250
3 ---- 8 ---- 100
3 ---- 8 ---- 200

I am then summing the Info column and grouping it by Title and Month, so my query now looks like this:

Title Month Info
1 ---- 7 ---- 200
1 ---- 8 ---- 450
2 ---- 7 ---- 300
2 ---- 8 ---- 600
3 ---- 7 ---- 500
3 ---- 8 ---- 300

What I would like to do is have the Group By Months display as individual columns, and the applicable Info data to appear underneath them. For example, this is what I want to appear:

Title --- 7 ----- 8
1 ---- 200 ---- 450
2 ---- 300 ---- 600
3 ---- 500 ---- 300

I'm not sure how to accomplish this task. If anyone has any ideas, I'd greatly appreciate it. Thanks!
Aug 31 '07 #1
Share this Question
Share on Google+
2 Replies


azimmer
Expert 100+
P: 200
Hi,

I have a SQL table that has data like this:

Title Month Info
1 ---- 7 ---- 100
1 ---- 7 ---- 100
1 ---- 8 ---- 200
1 ---- 8 ---- 250
2 ---- 7 ---- 150
2 ---- 7 ---- 150
2 ---- 8 ---- 300
2 ---- 8 ---- 300
3 ---- 7 ---- 250
3 ---- 7 ---- 250
3 ---- 8 ---- 100
3 ---- 8 ---- 200

I am then summing the Info column and grouping it by Title and Month, so my query now looks like this:

Title Month Info
1 ---- 7 ---- 200
1 ---- 8 ---- 450
2 ---- 7 ---- 300
2 ---- 8 ---- 600
3 ---- 7 ---- 500
3 ---- 8 ---- 300

What I would like to do is have the Group By Months display as individual columns, and the applicable Info data to appear underneath them. For example, this is what I want to appear:

Title --- 7 ----- 8
1 ---- 200 ---- 450
2 ---- 300 ---- 600
3 ---- 500 ---- 300

I'm not sure how to accomplish this task. If anyone has any ideas, I'd greatly appreciate it. Thanks!
If you only have 7 and 8 in the month column (or a small, fixed set) the following pattern should help:
Expand|Select|Wrap|Line Numbers
  1. select X.Title,
  2.           sum(case when X.Month=7 then X.Info else 0 end) as '7', 
  3.           sum(case when X.Month=8 then X.Info else 0 end) as '8' 
  4. from (
  5.     select Title, Month, sum(Info)
  6.     from yourTable
  7.     group by Title, Month ) as X
  8. group by X.Title
  9.  
Otherwise you need more elaborate solutions.
Aug 31 '07 #2

azimmer
Expert 100+
P: 200
...
Otherwise you need more elaborate solutions.
I've found this link useful: http://www.mssqltips.com/tip.asp?tip=937
Aug 31 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.