Sort of unpivotting on 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

Sort of unpivotting on sql server 2000

Author

Topic

chulheekim
Starting Member

46 Posts

Posted - 2009-01-27 : 12:08:05

I have a table that contain order header and line item together +_+

orderNum Item1 Item2 Item3 qty1 qty2 qty3
-------- ----- ----- ----- ---- ---- ----
123 Shirt Bike Chair 2 1 3
456 Desk 4

I want to output like below. I have my own that works good except that it generates three rows even there's only one line item.

OrderNum Item Qty
-------- ---- ---
123 Shirt 2
123 Bike 1
123 Chair 3
456 Desk 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:13:57

[code]
SELECT orderNum,Item1 AS Item,qty1 AS Qty
FROM Table
WHERE Item1 IS NOT NULL OR qty1 IS NOT NULL
UNION ALL
SELECT orderNum,Item2 AS Item,qty2 AS Qty
FROM Table
WHERE Item2 IS NOT NULL OR qty2 IS NOT NULL
UNION ALL
SELECT orderNum,Item3 AS Item,qty3 AS Qty
FROM Table
WHERE Item3 IS NOT NULL OR qty3 IS NOT NULL
[/code]

chulheekim
Starting Member

46 Posts

Posted - 2009-01-27 : 12:35:14

What was I thinking? Adding the where clause will do it.
thank you very very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:40:09

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources