Sql PIVOT and string concatenation aggregate

Question

I would like to use a pivot SQL query to construct a result table where the concatenate text as a result within the DATA section of the pivot table.

i.e. i have the following result from using a simple select:

 +------------+-----------------+---------------+ | Event Name | Resource Type   | Resource Name | +------------+-----------------+---------------+ | Event 1    | Resource Type 1 | Resource 1    | | Event 1    | Resource Type 1 | Resource 2    | | Event 1    | Resource Type 2 | Resource 3    | | Event 1    | Resource Type 2 | Resource 4    | | Event 1    | Resource Type 3 | Resource 5    | | Event 1    | Resource Type 3 | Resource 6    | | Event 1    | Resource Type 3 | Resource 7    | | Event 1    | Resource Type 4 | Resource 8    | | Event 2    | Resource Type 5 | Resource 1    | | Event 2    | Resource Type 2 | Resource 3    | | Event 2    | Resource Type 3 | Resource 11   | | Event 2    | Resource Type 3 | Resource 12   | | Event 2    | Resource Type 3 | Resource 13   | | Event 2    | Resource Type 4 | Resource 14   | | Event 2    | Resource Type 5 | Resource 9    | | Event 2    | Resource Type 5 | Resource 16   | +------------+-----------------+---------------+ 

And I would like to construct a result query that would look like this:

 +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+ | Event/Resource Type | Resource Type 1        | Resource Type 2        | Resource Type 3                       | Resource Type 4 | Resource Type 5                     | +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+ | Event 1             | Resource 1, Resource 2 | Resource 3, Resource 4 | Resource 5, Resource 6, Resource 7    | Resource 8      | NULL                                | | Event 2             | NULL                   | Resource 3             | Resource 11, Resource 12, Resource 13 | Resource 14     | Resource 1, Resource 9, Resource 16 | +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+ 

I know how to use a PIVOT statement in ms-sql but i don't know how to aggregate the Resource Name into a concatenation of comma separated items for each resource type.

P.S I could also use a solution using the Martix provided by SSRS 2008-R2 using Report Builde 3 with the first table as my data set and create a matrix that will aggregate the resource names into a comma separated string.

Answer 1

In order to get the result, first you should concatenate the values into the comma separated list.

I would use CROSS APPLY and FOR XML PATH:

SELECT distinct e.[Event Name],   e.[Resource Type],   LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName FROM yourtable e CROSS APPLY (     SELECT r.[Resource Name] + ', '     FROM yourtable r     where e.[Event Name] = r.[Event Name]       and e.[Resource Type] = r.[Resource Type]     FOR XML PATH('') ) r (ResourceName) 

See SQL Fiddle with Demo. The gives you result:

| EVENT NAME |   RESOURCE TYPE |                          RESOURCENAME | ------------------------------------------------------------------------ |    Event 1 | Resource Type 1 |                Resource 1, Resource 2 | |    Event 1 | Resource Type 2 |                Resource 3, Resource 4 | |    Event 1 | Resource Type 3 |    Resource 5, Resource 6, Resource 7 | |    Event 1 | Resource Type 4 |                            Resource 8 | |    Event 2 | Resource Type 2 |                            Resource 3 | |    Event 2 | Resource Type 3 | Resource 11, Resource 12, Resource 13 | |    Event 2 | Resource Type 4 |                           Resource 14 | |    Event 2 | Resource Type 5 |   Resource 1, Resource 9, Resource 16 | 

Then you will apply your PIVOT to this result:

SELECT [Event Name],   [Resource Type 1], [Resource Type 2],   [Resource Type 3], [Resource Type 4],   [Resource Type 5] FROM (   SELECT distinct e.[Event Name],     e.[Resource Type],     LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName   FROM yourtable e   CROSS APPLY   (       SELECT r.[Resource Name] + ', '       FROM yourtable r       where e.[Event Name] = r.[Event Name]         and e.[Resource Type] = r.[Resource Type]       FOR XML PATH('')   ) r (ResourceName) ) src pivot (   max(ResourceName)   for [Resource Type] in ([Resource Type 1], [Resource Type 2],                           [Resource Type 3], [Resource Type 4],                           [Resource Type 5]) ) piv 

See SQL Fiddle with Demo. Your final result will then be:

| EVENT NAME |        RESOURCE TYPE 1 |        RESOURCE TYPE 2 |                       RESOURCE TYPE 3 | RESOURCE TYPE 4 |                     RESOURCE TYPE 5 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- |    Event 1 | Resource 1, Resource 2 | Resource 3, Resource 4 |    Resource 5, Resource 6, Resource 7 |      Resource 8 |                              (null) | |    Event 2 |                 (null) |             Resource 3 | Resource 11, Resource 12, Resource 13 |     Resource 14 | Resource 1, Resource 9, Resource 16 | 

Answer 2

This works for me in SQL 2008, and it's dynamic - will handle additional Resource Type

Working SQLFiddle

IF OBJECT_ID('tempdb..#test') IS NOT NULL   DROP TABLE #test  GO  CREATE TABLE #test   (      eventName    VARCHAR(30),      resourceType VARCHAR(30),      resourceName VARCHAR(30)   );  INSERT INTO #test VALUES      ('Event 1','Resource Type 1','Resource 1'),             ('Event 1','Resource Type 1','Resource 2'),             ('Event 1','Resource Type 2','Resource 3'),             ('Event 1','Resource Type 2','Resource 4'),             ('Event 1','Resource Type 3','Resource 5'),             ('Event 1','Resource Type 3','Resource 6'),             ('Event 1','Resource Type 3','Resource 7'),             ('Event 1','Resource Type 4','Resource 8'),             ('Event 2','Resource Type 5','Resource 1'),             ('Event 2','Resource Type 2','Resource 3'),             ('Event 2','Resource Type 3','Resource 11'),             ('Event 2','Resource Type 3','Resource 12'),             ('Event 2','Resource Type 3','Resource 13'),             ('Event 2','Resource Type 4','Resource 14'),             ('Event 2','Resource Type 5','Resource 9'),             ('Event 2','Resource Type 5','Resource 16');  DECLARE @resourceTypes VARCHAR(max);  SELECT @resourceTypes = stuff((SELECT DISTINCT ',[' + resourceType + ']'                                FROM   #test                                FOR xml path('')), 1, 1, ''); DECLARE @query NVARCHAR(max);  SET @query = 'SELECT * FROM   (SELECT eventName,                resourceType,                stuff((SELECT '','' + resourceName + ''''                       FROM   #test b                       WHERE  a.eventName = b.eventName                              AND a.resourceType = b.resourceType                       FOR xml path('''')), 1, 1, '''') resourceName         FROM   #test a         GROUP  BY eventName,                   resourceType) AS data PIVOT (max(resourceName) FOR resourceType IN (' + @resourceTypes + ')) AS pvt';  EXEC(@query);  DROP TABLE #test;