T-sql :get SUM of Columns

Question

I have a table that looks something like the following :

          W1  W2 w3
Gold      10    2    3              
Silver     3    1    1

but i need a result :

          W1  W2  w3
Gold      10  12  15            
Silver     3  4  5

Is there any way i can get that result?

My sql query :

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

Answer 1

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

for older versions

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

Answer 2

Does this do what you want?

select t.??, t.w1, (t.w1 + t.w2) as w2, (t.w1 + t.w2 + t.w3) as w3
from table1 t;

I don't know what the name of the first column is, so I just used ??.

Answer 3

One thought, since you tagged this question with Reporting Services. If, in the end, you are displaying the info using Reporting Services I would highly consider using the Matrix tool to do the pivoting and summation of the data because that is exactly what it does.

To further explain as it seems you are going to use SSRS. Your matrix would have a dataset that would be similar to this:

SELECT
   [week]=DATEPART(ISO_WEEK,ta.enddate),
   ta.id,
   ta.MetalType as GoldorSilver
FROM  table1 ta 
where ta.enddate BETWEEN '2016/01/01' AND '2016/12/31'

The matrix would have a header and footer and column group would be [Week] with a Column Group total to do the sum across the week. The row group footer would do the sum across all weeks.