Base data set
expected data set
How to do that?
please check below SQL
SyntaxEditor Code Snippet
SEL CUST_ID , VAL1, CASE WHEN VAL1=1 THEN 'BOTH' WHEN VAL1=2 THEN VAL2||' ' ||'ONLY' ELSE 'ABROAD '||' ' ||'ONLY' END AS VAL FROM CUST order by 2 qualify row_number() over ( partition by cust_id , val1 order by val1)=1
You should apply conditional aggregation (assuming the Value2 is NOT NULL):
SELECT Cust_id, Value1, CASE -- no UK, only other countries WHEN Min(CASE WHEN Value2 = 'UK' THEN Value2 end) IS NULL THEN 'Abroad only' -- there is UK, but no other country WHEN Min(CASE WHEN Value2 <> 'UK' THEN Value2 end) IS NULL THEN 'UK only' ELSE 'BOTH' END FROM tab GROUP BY Cust_id, Value1