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
 Other SQL Server Topics (2005)
 crosstab

Author  Topic 

taniarto
Starting Member

27 Posts

Posted - 2013-06-29 : 02:29:42
Dear All,
I Have data for example :
ID qty
A001 100
A001 100
B001 200
B001 100
C001 500

I want to make view like :
ID A001 B001 C001
A001 200 0 0
B001 0 300 0
C001 0 0 500

is it possible ?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:58:12
[code]
SELECT ID,[A001],[B001],[C001]
FROM (SELECT ID,ID AS PivotID,SUM(qty) AS qty
FROM Table
GROUP BY ID)t
PIVOT(SUM(qty) FOR PivotID IN ([A001],[B001],[C001]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-23 : 05:19:04
And this is for dynamic PIVOT
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -