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

Table: CarType

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

CodePudding user response：

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

CodePudding user response：

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