将行数据分组和汇总到 MS-SQL 中的列中? [英] Group and sum row data into columns in MS-SQL?
问题描述
我不知道我应该打开一个新问题还是取消标记最后一个问题!所以,我想做的是:
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
我想创建一个新表,列出每个工作周,然后列出每个工作周内每个 catg 的总价格.这就是我现在的做法,但查询只是将每个 ww 的所有内容相加,给我所有工作周的相同总和:
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
推荐答案
你也可以这样做:
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.
pivot
语句也是一个非常合理的替代方案.我倾向于坚持使用显式版本(上图),因为它让我在添加列时更加灵活.
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.
这篇关于将行数据分组和汇总到 MS-SQL 中的列中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!