Moving data from several columns, into one column

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

Moving data from several columns, into one column

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-10-31 : 12:35:18

Right now I have data split into individual columns of type, "interior", "oceanview", "balcony" and "suite" and their associated rates.

I'm trying to put them all into 2 columns, one that specifies the type, and the other that specifies the rate.

basically like this

typeA typeB typeC TypeD

into

Type Rate
a
b
c
d

this is exactly the structure of what I have now

shipname saildate itinerary interiorprice oceanviewprice balconyprice suiteprice executiontime ratetype ship id_num detailslink
PA 2009-05-15 00:00:00 3 Day BAJA MEXICO from $249 * 359 399 NoBalcony NULL 07/01/2008 NULL Carnival Paradise 18 NULL

and this is the output I want

shipname saildate itinerary roomtype roomrate executiontime ratetype
pa 5/15/2009 3 day interior 249 7/1 null 18
pa 5/15/2009 3 day oceanview 359 7/1 null 18
pa 5/15/2009 3 day balcony no balc 7/1 null 18
pa 5/15/2009 3 day suite null 7/1 null 18

I'm not sure how to approach this task

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:41:13

[code]SELECT *
FROM
(
SELECT shipname, saildate, itinerary,'interior' as roomtype, interiorprice as price,executiontime,ratetype
FROM table
UNION ALL
SELECT shipname, saildate, itinerary,'oceanview', oceanviewprice,executiontime,ratetype
FROM table
UNION ALL
SELECT shipname, saildate, itinerary,'balcony', balconyprice,executiontime,ratetype
FROM table
UNION ALL
SELECT shipname, saildate, itinerary,'suite', suiteprice ,executiontime,ratetype
FROM table
)t[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:48:02

and if sql 2005 use UNPIVOT

SELECT *
FROM
(SELECT shipname, 
saildate, 
itinerary, 
interiorprice as interior,
oceanviewprice as oceanview,
 balconyprice as balcony,
 suiteprice as suite,
executiontime, 
ratetype
FROM Table) t
UNPIVOT (roomrate FOR roomtype IN ([interior], [oceanview], [balcony], [suite]))p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources