none
How to get this result

    Question

  • I want to get result as c1,c2,c3 by modifying the below Query

    WITH CTE AS
    (
    Select
    'c1' As A, NULL as B, Null as C
    UNION ALL
    SELECT
    NULL, 'c2',NULL
    Union ALL
    SELECT
    NULL, Null, 'c3'
    )
    SELECT * FROM CTE

    Please Help me to achieve   c1 as A c2 as B c3 as C by eliminating all nulls. 

     

    At present if I execute the result is as below : 

    Friday, September 13, 2013 10:48 AM

Answers

  • You asked for it, here it is:

    WITH CTE AS
    (
    Select
    'c1' As A, NULL as B, Null as C, 1 as dummy
    UNION ALL
    SELECT
    NULL, 'c2',NULL, 1 as dummy
    Union ALL
    SELECT
    NULL, Null, 'c3', 1 as dummy
    )
    SELECT
    min((case when A is not null then A else null end)) as A
    , min((case when B is not null then B else null end)) as B
    , min((case when C is not null then C else null end)) as C
    FROM CTE
    group by dummy

    But, important question is why are you doing this?


    Regards, Dean Savović

    • Marked as answer by KODI_KODI Friday, September 13, 2013 4:07 PM
    Friday, September 13, 2013 12:04 PM
  • WITH CTE AS
    (
    Select
    'c1' As A, NULL as B, Null as C
    UNION ALL
    SELECT
    NULL, 'c2',NULL
    Union ALL
    SELECT
    NULL, Null, 'c3'
    )
    SELECT MAX(A) A,MAX(B) B,MAX(C) C FROM CTE


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    • Marked as answer by KODI_KODI Friday, September 13, 2013 4:07 PM
    Friday, September 13, 2013 12:17 PM

All replies

  • You asked for it, here it is:

    WITH CTE AS
    (
    Select
    'c1' As A, NULL as B, Null as C, 1 as dummy
    UNION ALL
    SELECT
    NULL, 'c2',NULL, 1 as dummy
    Union ALL
    SELECT
    NULL, Null, 'c3', 1 as dummy
    )
    SELECT
    min((case when A is not null then A else null end)) as A
    , min((case when B is not null then B else null end)) as B
    , min((case when C is not null then C else null end)) as C
    FROM CTE
    group by dummy

    But, important question is why are you doing this?


    Regards, Dean Savović

    • Marked as answer by KODI_KODI Friday, September 13, 2013 4:07 PM
    Friday, September 13, 2013 12:04 PM
  • WITH CTE AS
    (
    Select
    'c1' As A, NULL as B, Null as C
    UNION ALL
    SELECT
    NULL, 'c2',NULL
    Union ALL
    SELECT
    NULL, Null, 'c3'
    )
    SELECT MAX(A) A,MAX(B) B,MAX(C) C FROM CTE


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    • Marked as answer by KODI_KODI Friday, September 13, 2013 4:07 PM
    Friday, September 13, 2013 12:17 PM