DYNAMIC WEEK PIVOTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DYNAMIC WEEK PIVOTS

Author

Topic

sheridanbman
Starting Member

10 Posts

Posted - 2009-07-31 : 13:02:45

Could someone please assist me with creating a dynamic pivot in t-sql.

I created a view that would output my results like this.

ORDERS SITE WEEK
27 NSS 1
24 NSC 1
17 NSM 1
12 NSC 2
11 NSM 2
78 NSS 2

I wanted to display my results in a dynamic pivot that would expand the weeks based on the data in the table, and not predefined weeks.

The view i'm looking at obtaining is

SITE [1] [2]
NSC 24 12
NSM 17 11
NSS 27 78

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-31 : 14:24:26

Here is a non-dynamic way to do it. How many possible [week] values are in your table? If it is just 1-52 then you can just extend the "[week] in ()" list.


declare @t table (ORDERS int, SITE varchar(10), WEEK int)
insert @t
select 27, 'NSS', 1 union all
select 24, 'NSC', 1 union all
select 17, 'NSM', 1 union all
select 12, 'NSC', 2 union all
select 11, 'NSM', 2 union all
select 78, 'NSS', 2

select p.* 
from   (select * from @T) s
pivot (max(orders) for [week] in ([1],[2])) p

OUTPUT:
SITE       1           2
---------- ----------- -----------
NSC        24          12
NSM        17          11
NSS        27          78

Be One with the Optimizer
TG

sheridanbman
Starting Member

10 Posts

Posted - 2009-07-31 : 18:25:36

Hi TG the query you gave me produced the following results. Its only suppose to display the site once and populate against the week columns. Any Idea?

select p.*
from (select * from vLanceOrders) s
pivot (max(QtyTotal) for [week] in ([1],[2])) p

ORDERS SITE WEEK 1 WEEK 2
2413 NSC 343801 NULL
6437 NSC NULL NULL
7230 NSC NULL NULL
8403 NSC NULL NULL
8733 NSC NULL NULL
8950 NSC NULL NULL
9978 NSC NULL NULL
10120 NSC NULL NULL
10363 NSC NULL NULL
10439 NSC NULL NULL
10487 NSC NULL NULL
10636 NSC NULL NULL
10747 NSC NULL NULL
11145 NSC NULL NULL
11799 NSC NULL NULL
11865 NSC NULL NULL
12000 NSC NULL 1667180
12171 NSC NULL NULL
12323 NSC NULL NULL
12836 NSC NULL NULL
12898 NSC NULL NULL
13510 NSC NULL NULL
16415 NSC NULL NULL
20 NSM NULL NULL
154 NSM NULL NULL
1592 NSM NULL NULL
1762 NSM 237368 NULL
4201 NSM NULL NULL
5052 NSM NULL NULL
5763 NSM NULL NULL
5813 NSM NULL NULL
6139 NSM NULL NULL
6791 NSM NULL NULL
7079 NSM NULL NULL
7086 NSM NULL NULL
7418 NSM NULL NULL
7439 NSM NULL NULL
7631 NSM NULL NULL
7683 NSM NULL NULL
7740 NSM NULL NULL
8120 NSM NULL NULL
8140 NSM NULL NULL
8358 NSM NULL NULL
8754 NSM NULL NULL
9027 NSM NULL NULL
9059 NSM NULL NULL
9123 NSM NULL NULL
9266 NSM NULL NULL
10101 NSM NULL 1388445
1415 NSS NULL NULL
2745 NSS 380623 NULL
4810 NSS NULL NULL
5376 NSS NULL NULL
5824 NSS NULL NULL
6157 NSS NULL NULL
6569 NSS NULL NULL
6756 NSS NULL NULL
6785 NSS NULL NULL
6861 NSS NULL NULL
6964 NSS NULL NULL
7099 NSS NULL NULL
7312 NSS NULL NULL
7343 NSS NULL NULL
7604 NSS NULL NULL
7882 NSS NULL 1075596
8263 NSS NULL NULL
8288 NSS NULL NULL
8299 NSS NULL NULL
8355 NSS NULL NULL
8540 NSS NULL NULL
8675 NSS NULL NULL
8727 NSS NULL NULL
8984 NSS NULL NULL
9370 NSS NULL NULL
9979 NSS NULL NULL
10653 NSS NULL NULL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-31 : 23:10:16

go through this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 00:16:11

quote:
Originally posted by sheridanbman
select p.*
from (select * from vLanceOrders) s
pivot (max(QtyTotal) for [week] in ([1],[2])) p

What column do you want to pivot ? QtyTotal or Orders ?

Is it a SUM() or MIN() or MAX() ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources