Single column into three columns

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 2008 Forums

Transact-SQL (2008)

Single column into three columns

Author

Topic

RogerSGI
Starting Member

9 Posts

Posted - 2011-01-27 : 15:39:58

Edit: I meant one column into three columns not three rows.
wait it's actually many rows into one row with three columns, geez long day

I have a table that is feed from XML.
I have three possibilities for info per ShipmentID but I need it to be queried into a single row with multiple columns

Input
58921 400017658 1
58921 Ref 2 2
59461 01025564 1
59461 0419521 2
59563 354 3

Query output
ID Col1 Col2 Col3
58921 400017658 Ref 2
59461 01025564 0419521
59563 354

This last ID 59563 the data is in Col3 while Col1 and Col2 are blank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-27 : 16:53:20

SELECT ShipmentID AS ID,
MAX(CASE WHEN TypeCol = 1 THEN DataCol ELSE NULL END) AS Col1,
MAX(CASE WHEN TypeCol = 2 THEN DataCol ELSE NULL END) AS Col2,
MAX(CASE WHEN TypeCol = 3 THEN DataCol ELSE NULL END) AS Col3
FROM dbo.Table1
GROUP BY ShipmentID
ORDER BY ShipmentID

N 56°04'39.26"
E 12°55'05.63"

RogerSGI
Starting Member

9 Posts

Posted - 2011-01-28 : 09:20:45

Sweet, thanks for the help.

Roger
N 44°49'24.47" W 93°34'56.31"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources