Use CASE statements to create distinct records based upon values in two fields
Posted on 2011-03-20
I have the following data in my table:
Cust_ID HasMembershipDoc HasDiscount
I'm trying to get distinct records per Cust_ID by doing this:
SELECT DISTINCT cust_id
, CASE WHEN "HasMembershipDoc" IS NULL AND "HasDiscount" IS NOT NULL THEN 'DiscountFlag Only'
WHEN "HasDiscount" IS NULL AND "HasMembershipDoc" IS NOT NULL THEN 'Doc Only'
WHEN "HasMembershipDoc" IS NOT NULL AND "HasDiscount" IS NOT NULL THEN 'FlagAndDoc'
END AS "Indicator"
but this won't work because the third WHEN statement will never happen.
How do I smerge the duplicates to get one Indicator column per Cust_ID?