PIVOT with monthly field - 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 2000 Forums

Transact-SQL (2000)

PIVOT with monthly field

Author

Topic

budi
Starting Member

5 Posts

Posted - 2010-08-19 : 05:13:22

Dear All,

Let’s say I have a table as below :

CREATE TABLE schedule (
PlantNo CHAR (10),
CompCode VARCHAR (20),
Budget MONEY,
Qty INT,
NextDue1 DATETIME,
NextDue2 DATETIME,
NextDue3 DATETIME)

INSERT INTO schedule VALUES
('DR501','1000-1','9500','1','7/8/2010','9/12/2010','11/25/2010')
INSERT INTO schedule VALUES
('DR502','1000-2','9000','2','9/8/2010','11/11/2010',NULL)
INSERT INTO schedule VALUES
('DR503','1000-3','8500','1','9/29/2010',NULL,NULL)
INSERT INTO schedule VALUES
('DR504','1000-3','8500','3','10/10/2010','12/6/2010',NULL)

I want to create pivot as below, with dynamic monthly column base on all due date occurred in the table, the budget value will fill in each month column, so I can get the total budget for each month.

Expected table
PlantNo CompCode Budget Qty NextDue1 Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10
DR501      1000-1 9500 1 8-Jul-10 9500 NULL 9500 NULL 9500 NULL
DR502      1000-2 9000 2 8-Sep-10 NULL NULL 9000 NULL 9000 NULL
DR503      1000-3 8500 1 29-Sep-10 NULL NULL 8500 NULL 8500 NULL
DR504      1000-3 8500 3 10-Oct-10 NULL NULL NULL 8500 NULL 8500

Please advise how I can make this happen, thanks in advance.

Salam,
Budi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:51:33

see

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources