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?

SQL queries can only return values, never columns. You can of course interpret the value as a column name. — , Aug 26 '09 at 10:11
yes you are correct, I'm infereing that since a column contains a non null value, that is the column I'm interested in. — Darknight, Aug 26 '09 at 10:18
How so, I know Pax talked about this being a bad schema design, however since I know the columns are not going to ever increase, do I really need to add all the extra complexity? including another look up table, plus more changes in the codebase? please expand — Darknight, Aug 26 '09 at 10:30

score 8 · Accepted Answer · answered Aug 26 '09 at 10:07

8

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

answered Aug 26 '09 at 10:07

Robin Day

100,552
23
116
167

Excellent this worked! sometimes simple is the best, don't know why I was over thinking the problem :o) – Darknight Aug 26 '09 at 10:16

score 4 · Answer 2 · answered Aug 26 '09 at 10:08

4

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

answered Aug 26 '09 at 10:08

Rob Farley

15,625
5
44
58

score 3 · Answer 3 · answered Aug 26 '09 at 10:13

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).

Thanks Pax for this nugged of information, I had thought about actually having only two columns, initially, eg: ModuleType, and ModuleID, but then I would need to maintain another table! but you are probably correct about the scaling. I think in this case I know the columns are very unlikey to change (eg scale up) — Darknight, Aug 26 '09 at 10:21
another table in the sense of a 'lookup' table for the ModuleID. also should have typed 'nugget' oops :o) — Darknight, Aug 26 '09 at 10:22

score 2 · Answer 4 · answered Aug 26 '09 at 10:09

2

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

answered Aug 26 '09 at 10:09

edosoft

17,121
25
77
111

score 0 · Answer 5 · answered Aug 26 '09 at 18:20

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.

SQL how to find non null column?

5 Answers5