How To display Multiple Rows on Single Row

Question

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 ?

welcome to SO. please have a look at [how-to-ask](http://stackoverflow.com/help/how-to-ask) What you are looking for is PIVOT. Please use the search for PIVOT, you will find MANY answers — swe, Nov 14 '18 at 11:24
I wouldn't use SQL for this. Use the programming language of your Website or app to care about the grid layout. — Thorsten Kettner, Nov 14 '18 at 11:25
No , there is no set amount of cars . Also , there is no fixed amount of person ids either so cant be hardcoded — Farrea69, Nov 14 '18 at 11:30
I suggest you use ROW_NUMBER() and partition by your person ID, you will then be able to pivot this data dynamically as per your requirement — Dohsan, Nov 14 '18 at 11:34
@ThorstenKettner I am just doing this as a sql query , no other language being used. dohsan I have looked at this but unsure how to shape the query — Farrea69, Nov 14 '18 at 11:37
*No , there is no set amount of cars . Also , there is no fixed amount of person ids either so cant be hardcoded* - It's a fairly terrible idea to do this in SQL. One of the basic tenets of SQL query outputs is that row count is variable, column count is fixed. To do this in SQL you'll have to write a dynamic pivoting sql that has all the possible columns at the time of the query.. And dynamic sql is also a terrible idea — Caius Jard, Nov 14 '18 at 11:44
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Joakim Danielson, Nov 14 '18 at 11:47
As Caius Jard has mentioned: this is not possible with mere SQL. What you could do is select an aggregated string, e.g. 'TOYOTA YARIS/2017/5000/10000, Nissan Micra/2001/1000/15000'. (String aggregation in SQL Server is a bit clumsy but possible.) — Thorsten Kettner, Nov 14 '18 at 11:58

score 0 · Accepted Answer · answered Nov 14 '18 at 11:58

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;

How To display Multiple Rows on Single Row

1 Answers1