title: Sql PIVOT and string concatenation aggregate
body: <p>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.</p>
  
  <p>i.e. i have the following result from using a simple select:</p>
  
  <pre>
  +------------+-----------------+---------------+
  | 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   |
  +------------+-----------------+---------------+
  </pre>
  
  <p>And I would like to construct a result query that would look like this:</p>
  
  <pre>
  +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
  | 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      | <i>NULL</i>                                |
  | Event 2             | <i>NULL</i>                   | Resource 3             | Resource 11, Resource 12, Resource 13 | Resource 14     | Resource 1, Resource 9, Resource 16 |
  +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
  </pre>
  
  <p>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>
  
  <p>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.</p>