SQL how to find non null column?

Question

I have a table with lots of columns, say I have columns

A, B, C, D

in each of these columns, only one column in any one record will be filled and the others will always be NULL.

I need a select statement that will return the Column of the non null Column.

I've tried coalesce, but this return a value, not the column to which the value belongs to.

Anyone know the simplest way to do this?

Answer 1

SELECT
    CASE
        WHEN A IS NOT NULL THEN 'A'
        WHEN B IS NOT NULL THEN 'B'
        WHEN C IS NOT NULL THEN 'C'
        WHEN D IS NOT NULL THEN 'D'
    END
FROM
    MyTable