Displaying weeks as columns

Question

Below is the script to display the number of weeks between the given dates.

SET DATEFIRST 1    
SELECT ta.account, ta.customer, SUM(amount), DATEPART(ww,ta.dt) WeekNumber
FROM tablename ta
WHERE dt >= '12/01/2011 00:00:00' 
      and dt < '12/29/2011 00:00:00'
GROUP BY ta.account, ta.customer, DATEPART(ww,ta.dt)

How do I display the diff weeks as diff columns in my result. Any suggestion would be helpful.

Sample O/P for the above is:

Date    Account         Customer    TotalSeconds    Amount  WeekNumber

2011-11-01  xx0918252   198303792R  394         2.99    45
2011-11-08  xx1006979   200100567G  92          0.16    46
2011-11-15  xx1005385   A6863744I   492         1.275   47
2011-11-21  xx1012872   D7874694G   770         0.52    48
2011-11-28  xx1006419   C7112151H   1904        2.64    49
2011-11-28  xx1006420   G7378945A   77          0.3     49

I want the O/P like:

Date     Account    Customer    TotalSeconds    Amount  WeekNumber45   WeekNumber46   WeekNumber47   WeekNumber8   WeekNumber49

and their corresponding data. Hope u understand my question. Thanks in advance.

Hi All, Thanks for the suggestions n help. Finally, I got the results that i wanted for time being. I still believe that it is hard coding. Is there a better solution for this. Thanks in advance. My code is as follows:

SELECT  ta.account, ta.customer, 
isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '49' THEN amount END),0) AS "Week49",

isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '50' THEN amount END),0) AS "Week50",

isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '51' THEN amount END),0) AS "Week51",

isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '52' THEN amount END),0) AS "Week52",

isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '53' THEN amount END),0) AS "Week53",

FROM (
select * from tablename
where dt >= '12/01/2011 00:00:00' and dt <= '12/31/2011 00:00:00'
) ta
group by ta.account, ta.customer

score 1 · Answer 1 · answered Jan 02 '12 at 20:39

First of all I would put your result in temporary table for later calculations. Let's imagine that following CTE is your result:

if object_id('tempdb..#tab') is not null drop table #tab

;with cte (Date,Account,Customer,TotalSeconds,Amount,WeekNumber) as (
    select cast('20111101' as datetime),'xx0918252','198303792R',394,2.99,45 union all
    select '20111108','xx1006979','200100567G',92,0.16,46 union all
    select '20111115','xx1005385','A6863744I',492,1.275,47 union all
    select '20111121','xx1012872','D7874694G',770,0.52,48 union all
    select '20111128','xx1006419','C7112151H',1904,2.64,49 union all
    select '20111128','xx1006420','G7378945A',77,0.3,49
)
select * into #tab from cte

Now your computed data is in #tab table and the following query returns pivoted table for those weeknumbers:

select date, account, customer, totalSeconds, amount, [45], [46], [47], [48], [49] from
(
    select date, account, customer, totalSeconds, amount, weeknumber as weeknumber from #tab
) src
pivot
(
    max(weeknumber) for weekNumber in ([45], [46], [47], [48], [49])
) pvt

Dynamic version of this query might look like this:

declare @sql nvarchar(max), @cols varchar(max)

select @cols = coalesce(@cols + ',', '') + '[' + cast(weeknumber as varchar) + ']' 
from (select distinct weeknumber from #tab) t
order by weeknumber

set @sql = N'
    select date, account, customer, totalSeconds, amount, ' + @cols + ' from
    (
        select date, account, customer, totalSeconds, amount, weeknumber as weeknumber from #tab
    ) src
    pivot
    (
        max(weeknumber) for weekNumber in (' + @cols + ')
    ) pvt
'

exec sp_executesql @sql

The result (in both cases):

date                    account   customer   totalSeconds amount     45     46     47     48     49
----------------------- --------- ---------- ------------ ---------- ------ ------ ------ ------ ------
2011-11-01 00:00:00.000 xx0918252 198303792R 394          2.990      45     NULL   NULL   NULL   NULL
2011-11-08 00:00:00.000 xx1006979 200100567G 92           0.160      NULL   46     NULL   NULL   NULL
2011-11-15 00:00:00.000 xx1005385 A6863744I  492          1.275      NULL   NULL   47     NULL   NULL
2011-11-21 00:00:00.000 xx1012872 D7874694G  770          0.520      NULL   NULL   NULL   48     NULL
2011-11-28 00:00:00.000 xx1006419 C7112151H  1904         2.640      NULL   NULL   NULL   NULL   49
2011-11-28 00:00:00.000 xx1006420 G7378945A  77           0.300      NULL   NULL   NULL   NULL   49

Hi Michal, Thanks for the effort. As I am new to SQL, I found little difficulty understanding your query. But, still working on it. Thanks once again. — sandysmith, Jan 05 '12 at 06:15
@sandysmith most important thing is [`pivot`](http://msdn.microsoft.com/en-us/library/ms177410.aspx) - it's used in first query and this is easy to write when there are know finite number of values that you trying transpose to columns - like `weeknumber`s in this specific case. It's a bit harder when it's not clear at the moment of writing query how many and what column you want to get and that is solved in 2nd query that generates column names *only* for values that are in column `weeknumber`. — Michał Powaga, Jan 05 '12 at 06:55
Most important thing is [`pivot`](http://msdn.microsoft.com/en-us/library/ms177410.aspx) - it's used in first query and this is easy to write when there are know finite number of values that you trying transpose to columns - like `weeknumber`s in this specific case. It's a bit harder when it's not clear at the moment of writing query how many and what column you want to get and that is solved in 2nd query that generates column names *only* for values that are in column `weeknumber`. — Michał Powaga, Jan 05 '12 at 06:56

score 0 · Answer 2 · answered Jan 02 '12 at 05:27

0

Take a look at the PIVOT function.

Tsql pivot command

answered Jan 02 '12 at 05:27

Tuck

203
3
8

1

The problem with pivot is that you have to be able to name the columns in advance. This will only work if sandysmith is willing to hard code the dates in question. – Gerald P. Wright Jan 02 '12 at 05:33
@Gerald: ur right. That's not what i want as the input dates may differ and also we don't the week number in advance. – sandysmith Jan 02 '12 at 05:48

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

T-SQL Pivot function combined with dynamic SQL. Examples:

edited May 23 '17 at 12:27

Community

1
1

answered Jan 02 '12 at 10:27

BartekR

3,827
3
24
33

Displaying weeks as columns

3 Answers3