This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to merge the driven car’s data like which car has driven by which drivers and form the aggregate the result for the same car.
Input Data:
|
1 2 3 4 5 6 7 8 |
CarName Rout Driver Year ---------- -------------------- ---------- ----------- Mobilio hyd - ahd Anvesh 2017 HCity ahd - raj Mukesh 2016 Mobilio kadi - abu Mukesh 2016 Duster mha - dlh Nupur 2017 Duster hyd - ahd Anvesh 2016 Duster bha - ahd Manish 2015 |
Expected Output:
|
1 2 3 4 5 |
CarName DrivenDetails ---------- ------------------------------------------------------------------------------------------------------ Duster Rout:mha - dlh, driven by Nupur in Year 2017, Rout:hyd - ahd, driven by Anvesh in Year 2016, Rout:bha - ahd, driven by Manish in Year 2015 HCity Rout:ahd - raj, driven by Mukesh in Year 2016 Mobilio Rout:hyd - ahd, driven by Anvesh in Year 2017, Rout:kadi - abu, driven by Mukesh in Year 2016 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE Cars ( CarName VARCHAR(10) ,Rout VARCHAR(20) ,Driver VARCHAR(10) ,Year INT ) GO INSERT INTO Cars VALUES ('Mobilio','hyd - ahd', 'Anvesh',2017) ,('HCity','ahd - raj', 'Mukesh',2016) ,('Mobilio','kadi - abu', 'Mukesh',2016) ,('Duster','mha - dlh', 'Nupur',2017) ,('Duster','hyd - ahd', 'Anvesh',2016) ,('Duster','bha - ahd', 'Manish',2015) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT b.CarName ,STUFF (( SELECT ', Rout:' + Rout + ', driven by ' + CAST(Driver AS VARCHAR(MAX)) + ' in Year ' + CAST(Year AS VARCHAR(MAX)) FROM Cars a WHERE ( a.CarName = b.CarName ) FOR XML PATH('') ) ,1,2,'') AS DrivenDetails FROM Cars b GROUP BY b.CarName |
Please try the different solutions for this puzzle and share it via comment...
