Transpose Rows to 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)

Transpose Rows to Columns

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-12-25 : 02:47:04

I have the below table which I would like to transpose

Crate ItemNo Item
P001 1 T001
P001 2 T101
P001 3 T006
P001 4 T090
P002 1 T011
P002 2 T111
P002 3 T056
P002 4 T080
P003 1 T004
P003 2 T103
P003 3 T072
P003 4 T091

To as below:

Crate Item1 Item2 Item3 Item4
P001 T001 T101 T006 T090
P002 T011 T111 T056 T080
P003 T004 T103 T072 T091

Can any one suggest some queries..

Thanks in advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 03:31:12

use pivot

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 03:35:11

try this

SELECT crate,[1] AS Item1,[2] AS Item2,[3] AS Item3,[4] AS Item4
FROM
(SELECT * FROM urtable) AS P
PIVOT ( item FOR itemno IN ([1],[2],[3],[4]))AS PVT

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-25 : 03:40:06

SELECT crate,[1] AS Item1,[2] AS Item2,[3] AS Item3,[4] AS Item4
FROM(SELECT * FROM yourtable) AS M
PIVOT
(MAX(Item) FOR ItemNo IN([1],[2],[3],[4])) AS P

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-25 : 05:09:22

Try This without using Pivot,,,

select crate,
max( case itemno when 1 then Item else NULL end ) as item1,
max( case itemno when 2 then Item else NULL end ) as item2,
max( case itemno when 3 then Item else NULL end ) as item3,
max ( case itemno when 4 then Item else NULL end ) as item4
from @temp
group by crate

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-12-25 : 08:49:47

Thanks Nages that was of great help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-25 : 10:26:50

quote:
Originally posted by bklr

try this

SELECT crate,[1] AS Item1,[2] AS Item2,[3] AS Item3,[4] AS Item4
FROM
(SELECT * FROM urtable) AS P
PIVOT ( item FOR itemno IN ([1],[2],[3],[4]))AS PVT

This won't work. You need to use Jaikrishna Approach.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 23:00:30

quote:
Originally posted by sodeep

quote:
Originally posted by bklr

try this

SELECT crate,[1] AS Item1,[2] AS Item2,[3] AS Item3,[4] AS Item4
FROM
(SELECT * FROM urtable) AS P
PIVOT ( item FOR itemno IN ([1],[2],[3],[4]))AS PVT

This won't work. You need to use Jaikrishna Approach.

Thanks Sodeep.
I had just missed Max(item value)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-26 : 00:00:44

WelCome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources