camelbrush camelbrush - 1 month ago 6
SQL Question

Group and sum row data into columns in MS-SQL?

Original Question

I didn't know If I should open a new question or just un-mark the last one!
So, What I want to do is:

WorkWeek Catg Item Cost
WorkWeek1 Cat1 Item1 Price
WorkWeek1 Cat1 Item2 Price
WorkWeek1 Cat2 Item3 Price
WorkWeek1 Cat3 Item4 Price
WorkWeek1 Cat3 Item1 Price
WorkWeek2 Cat1 Item1 Price
WorkWeek2 Cat2 Item2 Price
WorkWeek3 Cat1 Item1 Price
WorkWeek4 Cat1 Item2 Price
.
.
WorkWeekA CatB ItemC Price


I want to create a new table listing each workweek and then the total of each catg's price in that work week. This is how I am doing it right now, but the query just adds up everything for each ww giving me the same sum for all workweeks:

select
workweek
,(select sum(cost) from DataTable where Catg = 'Cat1') as Cat1TotalCost
,(select sum(cost) from DataTable where Catg = 'Cat2') as Cat2TotalCost
,(select sum(cost) from DataTable where Catg = 'Cat3') as Cat3TotalCost
.
.
.
.
from DataTable
group by Workweek

Answer

You can also do the pivot like this:

select workweek,
       sum(case when Catg = 'Cat1' then cost end) as Cat1TotalCost,
       sum(case when Catg = 'Cat2' then cost end) as Cat2TotalCost,
       sum(case when Catg = 'Cat3' then cost end) as Cat3TotalCost
from DataTable
group by Workweek

You should not be doing a separate subquery for each value.

The pivot statement is also a very reasonable alternative. I tend to stick with the explicit version (above), because it gives me more flexibilty in adding columns.