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

[nico3334]

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!

[azimmer]

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:

select X.Title,
          sum(case when X.Month=7 then X.Info else 0 end) as '7', 
          sum(case when X.Month=8 then X.Info else 0 end) as '8' 
from (
    select Title, Month, sum(Info)
    from yourTable
    group by Title, Month ) as X
group by X.Title
 

Otherwise you need more elaborate solutions.

[azimmer]

...
Otherwise you need more elaborate solutions.

I've found this link useful: http://www.mssqltips.com/tip.asp?tip=937