Merging multiple 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

SQL Server 2005 Forums

Transact-SQL (2005)

Merging multiple columns into one column

Author

Topic

wkolcz
Starting Member

1 Post

Posted - 2009-05-27 : 12:29:40

I have a database in which there are 3 columns per row of codes (time off codes).

What I need to do is to actually 'stack' them on top of each other and make one long column out of all of them.

So my table of:

tsCode1 tsCode2 tsCode3
HOL FRL HOM
FAM HPB ETW
HOL

could look like:

codes
HOL
FRL
HOM
FAM
HPB
ETW
HOL

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 12:57:53

do you have a unique valued column in your table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:35:32

insert table1 (codes)
select tscode1 from table2
union
select tscode2 from table2
union
select tscode3 from table2

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:36:45

insert table1 (codes)
select u.thevalue
from table2
unpivot (thevalue for thecol in (tscode1, tscode2, tscode3)
) as u

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 13:37:43

quote:
Originally posted by Peso

insert table1 (codes)
select tscode1 from table2
union
select tscode2 from table2
union
select tscode3 from table2

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

is union required? OPs output shows duplicate values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:40:18

Oh. Add "ALL" the the UNION parts if duplicates should be saved.
But it seems to me it's a typo. Why keep duplicate values in a "codes" column?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources