help with Pivoting in SQL Server 2000

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

help with Pivoting in SQL Server 2000

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-08 : 21:06:59

I have a table some thing like this

Tradedate Col1 Col2
01/01/2008 23 44
01/02/2008 34 43

the o/p should be some thing like this

------01/01---01/02
Col1--23------34
Col2--44------43

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 21:59:20

[code]DECLARE @TABLE TABLE
(
Tradedate datetime,
Col1 int,
Col2 int
)
INSERT INTO @TABLE
SELECT '01/01/2008', 23, 44 UNION ALL
SELECT '01/02/2008', 34, 43

SELECT Col,
SUM(CASE WHEN Tradedate = '20080101' THEN Value END) AS [01/01],
SUM(CASE WHEN Tradedate = '20080102' THEN Value END) AS [01/02]
FROM
(
SELECT Tradedate, Col = 'Col1', Value = Col1
FROM @TABLE
UNION ALL
SELECT Tradedate, Col = 'Col2', Value = Col2
FROM @TABLE
) t
GROUP BY Col
/*
Col 01/01 01/02
---- ----------- -----------
Col1 23 34
Col2 44 43

(2 row(s) affected)
*/[/code]

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

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-08 : 22:13:08

Thanks So much.

What should be the changes , If I have to run for last
five business dates.

say it the @TABLE , I populate the data on a daily basis
and every time I run I should run for the last five days.

Thx Again
Venu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 22:23:23

Replace the @table with your actual table name. @table is only for illustration purposes

For last 5 days
add where clause to the select . . . from @table statement

where TranDate >= dateadd(day, datediff(day, 0, getdate()), -5)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources