Dynamic PIVOT - 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 PIVOT

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-15 : 11:30:53

I need to be able to dynamically generate the date columns for my PIVOT result table.

My Sales Table contains data as follows:

partNo |period |Qty
12343758|2010-12 |1
43211020|2010-07 |2
32141437|2010-05 |3
12431414|2010-08 |2
12343758|2010-11 |1
43211020|2010-06 |2
32141437|2010-09 |3
12431414|2010-06 |2
43211020|2010-06 |2

The PIVOT query I've written looks like this:


SELECT part_number
, [2010-01]
, [2010-02]
, [2010-03]
, [2010-04]
, [2010-05]
, [2010-06]
, [2010-07]
, [2010-08]
, [2010-09]
, [2010-10]
, [2010-11]
, [2010-12]
FROM (
SELECT part_number,
period,Qty
FROM test) p PIVOT (SUM(Qty) FOR period in (
  [2010-01]
, [2010-02]
, [2010-03]
, [2010-04]
, [2010-05]
, [2010-06]
, [2010-07]
, [2010-08]
, [2010-09]
, [2010-10]
, [2010-11]
, [2010-12]]) ) as pvt

This works fine, except I need to be able to generate the column headings dynamically depending on which month it is because I always only want to look back on 6 months past the current month. Any help would be greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-15 : 16:47:51

search this site for "dynamic pivot". There are lots of posts about how to do it.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 10:45:43

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources