T-sql:获取列的总和 [英] T-sql :get SUM of Columns

问题描述

我有一张类似于以下内容的表格:

I have a table that looks something like the following :

          W1  W2 w3
Gold      10    2    3              
Silver     3    1    1

但我需要一个结果:

          W1  W2  w3
Gold      10  12  15            
Silver     3  4  5

有什么办法可以得到这个结果吗?

Is there any way i can get that result?

我的sql查询:

SELECT  
   week1=[1],week2=[2],week3=[3]
FROM
(
    SELECT
    [week]=DATEPART(ISO_WEEK,ta.enddate),ta.id
    FROM
    table1 ta where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
    SUM(id) FOR week IN ( 
        [1],[2],[3])
) piv

推荐答案

在透视数据之前计算运行总数

Calculate the running total before pivoting the data

SELECT element, 
       week1=[1],week2=[2],week3=[3]
FROM
(
SELECT [week] = DATEPART(ISO_WEEK,ta.enddate),
       price = sum(ta.price)Over(Partition by element Order by enddate),
       element  
FROM table1 ta 
where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
 SUM(price) FOR week IN ( [1],[2],[3])
) piv

对于旧版本

SELECT element, 
       week1=[1],week2=[2],week3=[3]
FROM
(
SELECT [week] = DATEPART(ISO_WEEK,ta.enddate),
       cs.price,
       element  
FROM table1 ta 
cross apply(select sum(price) from table1 tb 
            where ta.element = tb.element and ta.enddate >= tb.enddate ) cs (price)
where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'
) src
PIVOT
(
 SUM(price) FOR week IN ( [1],[2],[3])
) piv

这篇关于T-sql:获取列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！