I have a two column table that looks like the below

I want to be able to iterate through the table to dynamically generate case statements like the below

CASE WHEN CUSTOMER_NAME = 'Jim Smith' THEN 'Retail' 
     WHEN CUSTOMER_NAME = 'Nancy Jones' THEN 'Wholesale'  
     WHEN CUSTOMER_NAME = 'Mike Williams' THEN 'Retail'
     ...
ELSE NULL END AS CUSTOMER_TYPE

What is the best way to do this?

CodePudding user response：

This is a little weird. The SQL you've written is similar to saying -

select case when CUSTOMER_NAME = CUSTOMER_NAME then CUSTOMER_TYPE end as CUSTOMER_TYPE

customer_name is always equal to customer_name. You might as well just re-write this as

select CUSTOMER_TYPE

It might help to take a look at your full query to see if there may be a reason you're doing this. But the case statement here is mostly unnecessary.

CodePudding user response：

Here's a solution. It handles cases where the names and types include single quotes, too:

select    'case '
       || string_agg(replace(replace(replace('when customer_name = `@NM@` then `@TYP@`'
                    ,'@NM@' ,replace(customer_name,'''',''''''))
                    ,'@TYP@',replace(customer_type,'''',''''''))
                    ,'`'    ,'''')
         ,chr(10) || '     ')
       || chr(10) || 'end as customer_type'
from (values
     ('Jim Smith'      , 'Retail')
    ,('Nancy Jones'    , 'Wholesale')
    ,('Mike Williams'  , 'Retail')
    ,('Radar O''Reilly', 'It''s a Military Contract')
     )src(customer_name,customer_type)

Results:

case when customer_name = 'Jim Smith' then 'Retail'
     when customer_name = 'Nancy Jones' then 'Wholesale'
     when customer_name = 'Mike Williams' then 'Retail'
     when customer_name = 'Radar O''Reilly' then 'It''s a Military Contract'
end as customer_type