Ordering comma separated column values from related tables in SQL server

Question

I am using SQL server. I would like to have a query for below mentioned requirement. I am at Beginner level in SQL I have two tables as shown below:

Table: Car

Id	CarName	Company	LaunchYear
1	Audi Q7	Audi	2022
2	Ford GT	Ford	2021

Table: CarType

TypeId	CarId	Colour	PetrolRate	DieselRate	ElectricRate
1	1	PLATINUM	10000	9000	11000
2	1	GOLD	9500	8500	10500
3	1	SILVER	9250	8250	10250

I require the output in a single row for unique car.

It must have comma separated values of rates based on their colours.

The value of colours must be ordered by alphabetically ascending and the rates must be aligned with the colours.

I tried below query but I am getting multiple rows for a single car:

select * 
from Car c
left join CarType ct on ct.CarId= c.Id
where c.Id = 1

Please refer below required output:

Output

CarId	CarName	Company	LaunchYear	Colours	PetrolRates	DieselRates	ElectricRates
1	Audi Q7	Audi	2022	GOLD,PLATINUM,SILVER	9500,10000,9250	8500,9000,8250	10500,11000,10250

Answer Acceptance Reason:

I have accepted the solution which uses XML PATH because I am having SQL Server 2016.

Note: String_AGG() is for SQL Server 2017 and higher versions. The solution listed with it is also appropriate. (Only one solution can be accepted as an answer :))

Answer 1

This is achievable using FOR XML PATH string function since sql server 2005

select t1.id, t1.CarName, t1.Company, t1.LaunchYear, t2.Colours, t2.PetrolRates, t2.DieselRates, t2.ElectricRates
from Car t1
inner join
    (select  CarId
       ,stuff((SELECT ', ' + cast(Colour AS varchar(50)) [text()]
         from CarType 
         where CarId = t.CarId
         for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,' ') Colours
        ,stuff((SELECT ', ' + cast(PetrolRate AS varchar(50)) [text()]
         from CarType 
         where CarId = t.CarId
         for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,' ') PetrolRates
        ,stuff((SELECT ', ' + cast(DieselRate AS varchar(50)) [text()]
         from CarType 
         where CarId = t.CarId
         for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,' ') DieselRates
        ,stuff((SELECT ', ' + cast(ElectricRate AS varchar(50)) [text()]
         from CarType 
         where CarId = t.CarId
         for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,' ') ElectricRates
    from CarType t
    group by CarId) as t2 on t2.CarId = t1.id

Answer 2

We can use STRING_AGG( ~ , ',') to make a comma seperated list with WITHIN GROUP (ORDER BY TypeId) to be sure that the different values are in the right order.

create table cars (id int, carName varchar(25), Company varchar(25), LaunchYear int)
insert into cars values (1,'Audo Q7','Audi',2022),(2,'Ford GT','Ford',2021);
create table carType(TypeId int,CarId   int,Colour  varchar(10),PetrolRate  int,DieselRate  int,ElectricRate int);
insert into carType values
(1, 1,  'PLATINUM',10000,9000,  11000),
(2, 1,  'GOLD'    ,9500 ,8500,  10500),
(3, 1,  'SILVER'  ,9250 ,8250,  10250);

select
  c.id,
  c.carName,
  c.Company,
  c.LaunchYear,
  string_agg( Colour ,',') WITHIN GROUP (ORDER BY TypeId) Colour,
  string_agg( PetrolRate,',') WITHIN GROUP (ORDER BY TypeId) PetrolRate,
  string_agg( DieselRate,',') WITHIN GROUP (ORDER BY TypeId) DieselRate,
  string_agg( ElectricRate,',') WITHIN GROUP (ORDER BY TypeId) ElectricRate
from cars c
left join carType t
on c.id = t.carID
group by
  c.id,
  c.carName,
  c.Company,
  c.LaunchYear
GO

 id | carName | Company | LaunchYear | Colour               | PetrolRate      | DieselRate     | ElectricRate     
 -: | :------ | :------ | ---------: | :------------------- | :-------------- | :------------- | :----------------
  1 | Audo Q7 | Audi    |       2022 | PLATINUM,GOLD,SILVER | 10000,9500,9250 | 9000,8500,8250 | 11000,10500,10250
  2 | Ford GT | Ford    |       2021 | null                 | null            | null           | null             
 

db<>fiddle here