I have always lacked group concat function in SQL Server. Fortunately since SQL Server 2005 there is a possibility to carry out that functionality.
Here’s how I do that:
1. First sample data, a list of cars category and a list of vehicles belonging to them.
Declare @category Table(id int, name VarChar(13)); Insert Into @category Values(1, 'Passenger car'); Insert Into @category Values(2, 'Roadster'); Insert Into @category Values(3, 'MPV'); Insert Into @category Values(4, 'Off-roader'); Insert Into @category Values(5, 'Pickup'); Declare @car Table(categoryId int, name VarChar(21)); Insert Into @car Values(1, 'Chevrolet Corvette'); Insert Into @car Values(1, 'Porsche 911'); Insert Into @car Values(1, 'Ferrari 458 Italia'); Insert Into @car Values(1, 'Nissan Z-car'); Insert Into @car Values(1, 'Lamborghini Gallardo'); Insert Into @car Values(2, 'Audi TT'); Insert Into @car Values(2, 'Honda S2000'); Insert Into @car Values(2, 'Lotus Elise'); Insert Into @car Values(2, 'Mazda MX-5'); Insert Into @car Values(2, 'Porsche Boxster'); Insert Into @car Values(3, 'Mazda5'); Insert Into @car Values(3, 'Opel Zafira'); Insert Into @car Values(3, 'Ford C-Max'); Insert Into @car Values(3, 'Volkswagen Touran'); Insert Into @car Values(3, 'Peugeot 5008'); Insert Into @car Values(4, 'Cadillac Escalade EXT'); Insert Into @car Values(4, 'Chevrolet Suburban'); Insert Into @car Values(4, 'Range Rover'); Insert Into @car Values(4, 'Toyota Land Cruiser'); Insert Into @car Values(4, 'Jeep Commander'); Insert Into @car Values(5, 'Dodge Ram'); Insert Into @car Values(5, 'Ford F-150'); Insert Into @car Values(5, 'GMC Sierra'); Insert Into @car Values(5, 'Nissan Titan'); Insert Into @car Values(5, 'Toyota Tundra');
2. Query that will display a list of categories with theirs cars in one row per category.
Key functions are here “FOR XML PATH(”), TYPE“ which generates result as xml and “.value(‘.’, ‘VARCHAR(MAX)’)“ a XQUERY function for extracting values from given xml data.
SELECT c.name AS CATEGORY, (SELECT v.name + ', ' FROM @car v WHERE v.categoryId = id ORDER BY v.name FOR XML PATH(''), TYPE) .value('.', 'VARCHAR(MAX)') AS CARS FROM @category c;
So in the end my data looks like this:
CATEGORY CARS ------------- ------------------------------------------------------------------ Passenger car Chevrolet Corvette, Ferrari 458 Italia, Lamborghini Gallardo, Nissan Z-car, Porsche 911, Roadster Audi TT, Honda S2000, Lotus Elise, Mazda MX-5, Porsche Boxster, MPV Ford C-Max, Mazda5, Opel Zafira, Peugeot 5008, Volkswagen Touran, Off-roader Cadillac Escalade EXT, Chevrolet Suburban, Jeep Commander, Range Rover, Toyota Land Cruiser, Pickup Dodge Ram, Ford F-150, GMC Sierra, Nissan Titan, Toyota Tundra,
Trackbacks