Sql PIVOT and string concatenation aggregate

by

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 |

More Related Contents:

  1. SQL Server dynamic PIVOT query?
  2. Simple way to transpose columns and rows in SQL?
  3. T-SQL dynamic pivot
  4. TSQL Pivot without aggregate function
  5. Dynamically create columns sql
  6. TSQL PIVOT MULTIPLE COLUMNS
  7. Dynamic Pivot Columns in SQL Server
  8. How to pivot rows into columns (custom pivoting)
  9. How to create a PivotTable in Transact/SQL?
  10. Pivot data in T-SQL
  11. Using PIVOT in SQL Server 2008
  12. Flattening of a 1 row table into a key-value pair table
  13. How to calculate age (in years) based on Date of Birth and getDate()
  14. CROSS JOIN vs INNER JOIN in SQL
  15. Convert Month Number to Month Name Function in SQL
  16. Function vs. Stored Procedure in SQL Server
  17. Multiple INSERT statements vs. single INSERT with multiple VALUES
  18. Limit on the WHERE col IN (…) condition
  19. Removing leading zeroes from a field in a SQL statement
  20. Getting result of dynamic SQL into a variable for sql-server
  21. How to get difference between two rows for a column field?
  22. SQL Server SELECT into existing table
  23. How can I include null values in a MIN or MAX?
  24. How to get the identity of an inserted row?
  25. What are the use cases for selecting CHAR over VARCHAR in SQL?
  26. How do I format date and time on ssrs report?
  27. TSQL – How to use GO inside of a BEGIN .. END block?
  28. SQL – How do I get only the numbers after the decimal?
  29. How to retrieve records for last 30 minutes in MS SQL?
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment