CASE statement results for multiple fields

Question

Running PostgreSQL 7.x (Yeah I'm upgrading)

Example:

SELECT
    CASE 
        WHEN "substring"(t."Field"::text, 0, 3) = '01'::text THEN 'group one'::text
        WHEN "substring"(t."Field"::text, 0, 4) = '123'::text THEN 'group one'::text
        WHEN "substring"(t."Field"::text, 0, 5) = '4567'::text THEN 'group two'::text
        WHEN "substring"(t."Field"::text, 0, 6) = '99999'::text THEN 'group three'::text
        WHEN "substring"(t."Field"::text, 0, 3) = '07'::text THEN 'group three'::text
        ELSE NULL::text
    END AS new_field,
    CASE 
        WHEN "substring"(t."Field"::text, 0, 3) = '01'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 4) = '123'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 5) = '4567'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 6) = '99999'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 3) = '07'::text THEN 'subgroup b'::text
        ELSE NULL::text
    END AS another_new_field,...

Is there a way to have one case statement give two fields as a result as the data processed is the same it's just the label that's different.

Without specifics it's hard to say for sure, but that sounds like a structure that violates 3NF, if the logic for two columns' values is identical. Maybe not, since you're pulling from two places, but as I said it's hard to tell. — Tom H, Sep 12 '11 at 17:52
yeah pulling from one source but one is a group and one is a subgroup, sry the example doesn't really show this — Phill Pafford, Sep 12 '11 at 17:56

score 0 · Accepted Answer · answered Sep 12 '11 at 18:13

I'm not familiar with Postgre, but perhaps You can try a common table expression to build a table indexing your lookup conditions and the output results for group and subgroup, which might look something like this (of course the conditions could come from an actual table as well...):

with Lookup as (
    select 0 as start, 3 as end,
        '01' as match, 'group one' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 4 as end,
        '123' as match, 'group one' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 5 as end,
        '4567' as match, 'group two' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 6 as end,
        '99999' as match, 'group three' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 3 as end,
        '07' as match, 'group three' as sgroup, 'subgroup b' as subgroup union
)
select
    L.sgroup as new_field,
    L.subgroup as another_new_field
from table T
left join Lookup L on "substring"(T."Field"::text, L.start, L.End) = L.match

Then you only have to define the conditions once, and match them once to get both fields outputted. As expressed by @Tom in comment, this kind of scenario is not well handled in SQL, so you're left with a more "hacky" solution like the above.

Best Regards,

CASE statement results for multiple fields

1 Answers1