Search code examples
mysqlsqldataframerowdata-cleaning

Convert rows to columns to remove duplicate entries on common value in SQL

I’m a beginner to SQL and trying to figure out how to convert data to a column to remove duplicate entries. I’ve tried pivot, but I don’t need to aggregate values. Any help would be appreciated.

Current state:

ClientID AddressType Country
10001 Home AU
10001 Postal NZ

Target state:

ClientID Home Postal
10001 AU NZ
Solution

  • You can easily accomplish this using aggregation of a case expression

    select clientId, 
    Max(case when AddressType='Home' then Country end) Home,
    Max(case when AddressType='Postal' then Country end) Postal
from clients
group by ClientId