Combine multiple columns into one columns

Question

Post reply

Combine multiple columns into one columns

TT-131210

SSCommitted

Points: 1603
More actions
March 17, 2011 at 2:46 pm

#233351

I need to get the following results when combining multiple columns into one column.
Col0 Col1 Col2 Col3
A null null
null B null
null null C
Results:
Col
A
B
C

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Have you looked into COALESCE()? It should do what you need.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

dant12 SSCrazy Points: 2712 More actions · Answer 2

you need UNPIVOT

DECLARE @a TABLE (COL1 VARCHAR, COL2 VARCHAR,COL3 VARCHAR)

INSERT INTO @a

SELECT 'A',NULL,NULL UNION ALL

SELECT NULL,'B',NULL UNION ALL

SELECT NULL,NULL,'C'

SELECT X

FROM

(

SELECT *

FROM @a

) A

UNPIVOT (X FOR Y IN (COL1,COL2,COL3)) P

--
Thiago Dantas
@DantHimself

TT-131210 SSCommitted Points: 1603 More actions · Answer 3

Both examples work great but COALESCE() is simple.

Thank you

dant12 SSCrazy Points: 2712 More actions · Answer 4

TT-131210 (3/17/2011)
Both examples work great but COALESCE() is simple.
Thank you

coalesce will only work if you always only have 1 column filled, if col1 = 'a' and col2 = 'b' coalesce will display only col1

but this case is where you need to know your data, i don't

--
Thiago Dantas
@DantHimself

TT-131210 SSCommitted Points: 1603 More actions · Answer 5

I did not know that but COALESCE will work for my data. There will always be only one value for the three columns.

I will keep the other example as well.

Thanks Tim