SQL Server 2000 - PIVOTING - 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

General SQL Server Forums

New to SQL Server Programming

SQL Server 2000 - PIVOTING

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-11-29 : 21:06:10

I need some help here in Pivoting the table.
I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.

I can write a stored Proc using cursor, but I just want to learn how
to do it with using cursors

TD-------- Acct------ RouteID----Symbol---- Quantity----
---------- ---------- ---------- ---------- -----------
11/26/2007 40B91209-- CSFB------ GBL--------100
11/26/2007 40B91209-- SIGMA-X----TDY--------100
11/26/2007 4W3L1209-- CSFB------ BIDZ------ 1300
11/26/2007 4W3L1209-- CSFB------ SURW------ 100
11/27/2007 HFS10003-- SIGMA-X----URBN------ 500
11/27/2007 RAM10001-- ISE--------DSCP------ 300
11/27/2007 RAM10001-- SIGMA-X----HYC--------468
11/28/2007 HFS10003-- CSFB------ ARO--------5900
11/28/2007 HFS10003-- CSFB------ CAB--------1300
11/28/2007 HFS10003-- CSFB------ PNRA------ 4600
11/29/2007 RAM10001-- CSFB------ DSCP------ 175
11/29/2007 HFS10003-- CSFB------ CL-------- 220
11/29/2007 WIL10008-- SIGMA-X----CBM--------1400

The output should look some thing like this. If some can help me

AcctNum----RouteID--symbol--MON--TUE--Wed--THU---- FRI
WIL10008---SIGMA-X--CBM-----0----0----0----1400-- 0

Thanks for any help.

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-11-29 : 21:49:42

I guess I got a hang of it... Thanks anyways

SELECT Account ,
RouteID ,
Symbol ,
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 1 THEN Quantity ELSE 0 END) AS 'Sunday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 2 THEN Quantity ELSE 0 END) AS 'Monday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 3 THEN Quantity ELSE 0 END) AS 'Tuesday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 4 THEN Quantity ELSE 0 END) AS 'Wednesday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 5 THEN Quantity ELSE 0 END) AS 'Thursday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 6 THEN Quantity ELSE 0 END) AS 'Friday',
SUM(CASE WHEN DATEPART ( dw , Tradedate ) = 7 THEN Quantity ELSE 0 END) AS 'Saturday'
FROM TradesONLINE..ATSExecutions
WHERE Tradedate BETWEEN '11/25/2007' AND '12/01/2007'
GROUP BY Account ,
RouteID ,
Symbol

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-30 : 00:55:42

Also always express your dates in YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources