SQL CASE THEN statement where ID has multiple values

Question

I have to write a query to get the final output of one person ID with flags if they own a car or truck.

Expected output:

Person_ID | Truck | Car 
   1         Y       Y

The query that I wrote pulls the data from a table that has multiple ID's so the output has the ID repeated.

Base Data:

SELECT DISTINCT PERSON_ID, VEHICLE_TYPE 
FROM CARS

Output:

PERSON_ID | VEHICLE_TYPE
  1         Car
  1         Truck

My Query:

SELECT PERSON_ID, 
CASE WHEN VEHICLE_TYPE = 'TRUCK' THEN 'Y' ELSE 'N' END AS TRUCK,
CASE WHEN VEHICLE_TYPE = 'CAR' THEN 'Y' ELSE 'N' END AS CAR
FROM (
SELECT DISTINCT PERSON_ID, VEHICLE_TYPE
FROM CARS
)

Final Output:

Person_ID | Truck | Car 
1           Y       N
1           N       Y

The person has both a truck and a car but the ID is being repeated. Is there any way to solve this problem?

Answer 1

You can do conditional aggregation:

select 
    person_id,
    max(case when vehicle_type = 'TRUCK' then 'Y' else 'N' end) truck,
    max(case when vehicle_type = 'CAR' then 'Y' else 'N' end) car
from cars
group by person_id