Pfthb

I am working on a query which contains multiple rows per a particular id and im looking to display them all on one line.

The query I currently have is :

SELECT 

person_id,
car_name,
car_year, 
car_value,
car_mileage

FROM tbl_motor_vehicles

This returns the below:

person_id car_name car_year car_value car_mileage
---------------------------------------------------------------------
1 TOYOTA YARIS 2017 5000 10000
1 Nissan Micra 2001 1000 15000
2 Golf 15000 700000
2 Fiat Punto 2002 2500 60000

I need this to display on one line per id with each car getting its own column like below :

Person ID Car_1_NAME Car_1_year car1_value car1_mileage Car_2_NAME Car_2_year car2_value car2_mileage
--------------------------------------------------------------------------------------------------------------------------------------
1 TOYOTA YARIS 2017 5000 10000 Nissan Micra 2001 1000 15000
2 Golf 2007 15000 700000 Fiat Punto 2002 2500 60000

Can anyone advise me how to go about this ?

A SQL query has a fixed number of columns. So, you can only show a fixed set of cars for each person. With that caveat, you can use conditional aggregation:

SELECT person_id,
 MAX(CASE WHEN seqnum = 1 THEN car_name END) as car_name_1,
 MAX(CASE WHEN seqnum = 1 THEN car_year END) as car_year_1,
 MAX(CASE WHEN seqnum = 1 THEN car_value END) as car_value_1,
 MAX(CASE WHEN seqnum = 1 THEN car_mileage END) as car_mileage_1,
 MAX(CASE WHEN seqnum = 2 THEN car_name END) as car_name_2,
 MAX(CASE WHEN seqnum = 2 THEN car_year END) as car_year_2, 
 MAX(CASE WHEN seqnum = 2 THEN car_value END) as car_value_2,
 MAX(CASE WHEN seqnum = 2 THEN car_mileage END) as car_mileage_2
FROM (SELECT mv.*, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY (SELECT NULL)) as seqnum
 FROM tbl_motor_vehicles mv
 ) mv
GROUP BY person_id;