Sql PIVOT and string concatenation aggregate

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) ResourceNameFROM yourtable eCROSS 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)) srcpivot(  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 |

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 #testGOCREATE TABLE #test  (     eventName    VARCHAR(30),     resourceType VARCHAR(30),     resourceName VARCHAR(30)  );INSERT INTO #testVALUES      ('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; 

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: