mbielanczuk

Group concat in SQL Server 2005/2008

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,