Pivot on multiple columns - 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 2005 Forums

Transact-SQL (2005)

Pivot on multiple columns

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2012-02-02 : 02:13:06

I've the following data
Order table
OrderNo Item Qty
1 A 4
1 B 3
1 C 2
2 A 2
2 C 2

OrderSize Breakup
OrderNo Item Size Qty
1 B M 1
1 B L 1
1 B XL 1
1 C XL 2
2 C L 1
2 C M 1

Please note that not all items will have the size attribute to it. In the above example items B & C have sizes. Hence they are present in the ordersizebreakup table.

The output should be in the following format. The items should appear as columns along with their sizes
OrderNo A B-S B-M B-L B-XL C-S C-M C-L C-XL
1 4 0 1 1 1 0 0 0 2
2 2 0 0 0 0 0 1 1 0

Any help is appreciated

Thanks
Sridhar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 11:42:29

do you've a master table for storing size values? without which how do you list other sizes like S. i can not even see them on OrderSize table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources