Post reply

Combine multiple columns into one columns

  • 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

  • March 17, 2011 at 2:51 pm

    #1300239

    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

  • March 17, 2011 at 2:51 pm

    #1300241

    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

  • March 17, 2011 at 2:56 pm

    #1300243

    Both examples work great but COALESCE() is simple.

    Thank you

  • March 17, 2011 at 2:59 pm

    #1300244

    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

  • March 17, 2011 at 3:03 pm

    #1300245

    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

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

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