Dirk Marine Dirk Marine - 7 months ago 10
SQL Question

SQL Select Columns.. IF NULL then Select other Columns

I have a view like this:

ID| Key | Product | Item | Block | Source | Title | Text | Type | H1 | H2 | H3 |
-------------------------------------------------------------------------------
1 | 456 | abcd | def | 1 | TP | QWERT | YUIP | tgr | A1 | A2 | A3 |
2 | 567 | fhrh | klo | 1 | GT | TREWQ | ITGF | trp | A1 | A2 | A3 |
3 | 891 | ufheu | yut | 2 | FR | WERTY | MNBV | uip |NULL|NULL|NULL|


I want to export some of these columns into a existing, empty table. I want to select the first six columns and then select the other columns like an hierarchy going from right to left.

If H1, H2 and H3 are NOT null, they should come in the output and Title, Text and Type should be NULL (even though they contain values).

If H1, H2 and H3 are NULL, i want thet Title, Text and Type to be in the output.

It should be something like this:

ID| Key | Product | Item | Block | Source | Title | Text | Type | H1 | H2 | H3 |
-------------------------------------------------------------------------------
1 | 456 | abcd | def | 1 | TP | NULL | NULL | NULL | A1 | A2 | A3 |
2 | 567 | fhrh | klo | 1 | GT | NULL | NULL | NULL | A1 | A2 | A3 |
3 | 891 | ufheu | yut | 2 | FR | WERTY | MNBV | uip |NULL|NULL|NULL|


Can anybody help me with this? Help would be very much appreciated!

Answer

Ok, I've wrapped all column names in [square brackets] because you're using reserved names (Key, Text, Type) and I like consistency, it's worth breaking this habit as soon as possible.

If your criteria is that all three columns (H1, H2, H3) need to be NULL then you'll want something like this;

SELECT [ID]
    ,[key]
    ,[Product]
    ,[Item]
    ,[Block]
    ,[Source]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Title]
        ELSE NULL
        END AS [Title]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Text]
        ELSE NULL
        END AS [Text]
    ,CASE 
        WHEN H1 IS NULL
            AND H2 IS NULL
            AND H3 IS NULL
            THEN [Type]
        ELSE NULL
        END AS [Type]
    ,H1
    ,H2
    ,H3
FROM DataTable
Comments