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.

Plaque

#10295346 See this answer re: FOR XML PATH – Yasukoyataghan

There is a CLR Concate aggregate function available, that you could add to your database. I believe then you'd be able to do a pivot easily to do this since you'd just use a regular pivot but instead of where you'd use an aggregate SUM/MAX/etc. you'd use the aggregate Concatenate function. There's an example of aggregate concatenate here, there's other around the internet: msdn.microsoft.com/en-us/library/ms182741.aspx It is really a useful function. – Grasmere

@Grasmere Thank you .. it is really us full but unfortunately I could not create aggregates as the client can only runs simple select queries and he will never get permissions to create a new CLR aggregate .. – Plaque

@Yasukoyataghan How would I pivot the above table using the answer you provided ? – Plaque

@Plaque - answer posted using comment example – Yasukoyataghan

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 |

Bursary

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; 

Yasukoyataghan

Sometimes it will require to execute query using EXECUTE sp_executesql @query; if you get "Must declare the scalar variable "@query" " error. – Tolan

Answer 3

In report builder you should use the Table or Matrix wizard and do the following:

• Resource Type field as the column groups.
• Event Name field as the row groups.
• And Resource Name field as details, you will have to use an aggregatre function such as Count.

At this stage complete the wizard then once complete edit the Resource Name cell as an expression. Replace the expression with:

=Join( LookupSet( Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
                  Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
                  Fields!RESOURCE_NAME.Value, "DataSet1"), ", ")

Now tried and tested:

Broadleaved

Answer 4

Full working example:

SET NOCOUNT ON
GO

    DECLARE @SourceTable TABLE
    (
         EventName NVARCHAR(10)
        ,ResourceType NVARCHAR(20)
        ,ResourceName NVARCHAR(20)
    )

    INSERT INTO @SourceTable(EventName,ResourceType,ResourceName)
    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') 

    ;WITH SourceTable AS
    (
        SELECT DISTINCT ST1.EventName
                       ,ST1.ResourceType
                       ,(SELECT SUBSTRING((SELECT ',' +ResourceName 
                                           FROM @SourceTable AS ST2
                                           WHERE ST1.EventName=ST2.EventName AND ST1.ResourceType=ST2.ResourceType 
                                           FOR XML PATH('')),2,200) AS CSV) AS ResourceName
        FROM @SourceTable AS ST1
    )
    SELECT    EventName
            ,[Resource Type 1]
            ,[Resource Type 2]
            ,[Resource Type 3]
            ,[Resource Type 4]
            ,[Resource Type 5]
    FROM 
    (
        SELECT EventName
              ,ResourceType
              ,ResourceName
        FROM SourceTable
    ) PivotSource
    PIVOT
    (
        MAX(ResourceName) FOR ResourceType  IN ([Resource Type 1],[Resource Type 2],[Resource Type 3],[Resource Type 4],[Resource Type 5])
    ) PivotTable

SET NOCOUNT OFF
GO

Fictionalize