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?

Thanks in advanced

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

Answer 2

Try case...

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 as NotNullCol, OtherCols
FROM YourTable

Answer 3

Not pretty but this does what you want:

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

Answer 4

Whenever you find yourself trying to do things with multi-column sets, you've probably got your schema wrong.

It would almost certainly be easier to separate A, B, C and D into separate rows in a separate table, tie them back to the row in the original table and create a JOIN-type query.

Alternatively, if only one is ever non-NULL, I would opt for two columns, type (A, B, C or D) and value. Then you're not wasting the columns in every row, and the queries are immeasurably easier (assuming the types are the same).

However, you can do it this way with case:

select case
    when A is not null then 'A'
    when B is not null then 'B'
    when C is not null then 'C'
    else                    'D'
    end
from ...

The syntax may not be exactly correct, you'll need to look it up. IIt's not something I usually do since I consider it a bad idea (per-row functions in select never scale well).

Answer 5

I would redesign. It almost alawys turns out to be a bad idea to store data this way instead of in a related table.

In a related table you can more easily query the information. You can also more easily put a constraint on the data that only one record is allowed, so you will will probably have better data integrity.

Fewer tables does not necessarily mean faster access or better design in relational databases. Porperly designed and indexed related tables are generally quite good. And while you may think you won't ever need a fifth column, business rules change and I've seen many things that people thought wouldn't need expansion that turned out to need the scalibilty.