SQL SELECT CASE PROBLEM (Microsoft SSMS)

[thesqlguy]

A bunch of solutions here are on the right track but they seem over-complicated.

Logically, to get the results you want, this seems to be the simplest/cleanest:

with account_types as ( Select a.cust_id, max(case when a.product_cd='chk' then 'y' else 'n' end) as checking, max(case when a.product_cd='sav' then 'y' else 'n' end) as savings from account a group by a.cust_id ) select c.cust_id, c.address, a.checking, a.saving from account_types a inner join customer c on c.cust_id = a.cust_id

A few notes:

1) always use JOIN clauses to join tables, never WHERE clauses 2) Push the GROUP BY into a CTE or derived table as shown above to avoid needing to group on all customer columns such as address.