# 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

• 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 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

• Marked as answer by 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 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

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