row results records as colums - 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)

row results records as colums

Author

Topic

raviravi
Starting Member

8 Posts

Posted - 2006-09-28 : 02:13:17

dear all
pls help
i am using sql server 2000
i have this result set
---------------------------
customer month sale
---------------------------
A may 100
B june 100
C july 50
D may 200
E june 40
F july 100
G january 60
Now i want the result in this manner
-----------------------------------------
customer may june july januray
----------------------------------------
A 100
D 200
B 100
E 40
F 100
G 60

-----------------------------------------
friends is it possible if so then how pls explain.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 02:46:49

[code]declare @pivot table (customer varchar, month varchar(10), sale int)

insert @pivot
select 'A', 'may', 100 union all
select 'B', 'june', 100 union all
select 'C', 'july', 50 union all
select 'D', 'may', 200 union all
select 'E', 'june', 40 union all
select 'F', 'july', 100 union all
select 'G', 'january', 60

SELECT Customer,
SUM(CASE WHEN Month IN ('January', 'Jan') THEN Sale ELSE 0 END) 'Jan',
SUM(CASE WHEN Month IN ('February', 'Feb') THEN Sale ELSE 0 END) 'Feb',
SUM(CASE WHEN Month IN ('March', 'Mar') THEN Sale ELSE 0 END) 'Mar',
SUM(CASE WHEN Month IN ('April', 'Apr') THEN Sale ELSE 0 END) 'Apr',
SUM(CASE WHEN Month IN ('May') THEN Sale ELSE 0 END) 'May',
SUM(CASE WHEN Month IN ('June', 'Jun') THEN Sale ELSE 0 END) 'Jun',
SUM(CASE WHEN Month IN ('July', 'Jul') THEN Sale ELSE 0 END) 'Jul',
SUM(CASE WHEN Month IN ('August', 'Aug') THEN Sale ELSE 0 END) 'Aug',
SUM(CASE WHEN Month IN ('September', 'Sep', 'Sept') THEN Sale ELSE 0 END) 'Sep',
SUM(CASE WHEN Month IN ('October', 'Oct') THEN Sale ELSE 0 END) 'Oct',
SUM(CASE WHEN Month IN ('November', 'Nov') THEN Sale ELSE 0 END) 'Nov',
SUM(CASE WHEN Month IN ('December', 'Dec') THEN Sale ELSE 0 END) 'Dec'
FROM @Pivot
GROUP BY Customer
ORDER BY Customer[/code]

Peter Larsson
Helsingborg, Sweden

raviravi
Starting Member

8 Posts

Posted - 2006-09-28 : 04:41:43

Thanks a lot...
friend it served my purpose
thanks once again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 08:32:42

Also refer
http://weblogs.sqlteam.com/jeffs/archive/2005/05.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