Sql PIVOT 和字符串连接聚合

Question

我想使用一个数据透视 SQL 查询来构建一个结果表，其中连接文本作为数据透视表的数据部分中的结果。

即我使用简单的选择有以下结果:

+------------+-----------------+--------------+
|事件名称 |资源类型 |资源名称 |
+------------+-----------------+--------------+
|事件一 |资源类型 1 |资源 1 |
|事件一 |资源类型 1 |资源 2 |
|事件一 |资源类型 2 |资源 3 |
|事件一 |资源类型 2 |资源 4 |
|事件一 |资源类型 3 |资源 5 |
|事件一 |资源类型 3 |资源 6 |
|事件一 |资源类型 3 |资源 7 |
|事件一 |资源类型 4 |资源 8 |
|事件二 |资源类型 5 |资源 1 |
|事件二 |资源类型 2 |资源 3 |
|事件二 |资源类型 3 |资源 11 |
|事件二 |资源类型 3 |资源 12 |
|事件二 |资源类型 3 |资源 13 |
|事件二 |资源类型 4 |资源 14 |
|事件二 |资源类型 5 |资源 9 |
|事件二 |资源类型 5 |资源 16 |
+------------+-----------------+--------------+

我想构建一个如下所示的结果查询:

+---------------------+------------------------+-- ----------------------+---------------------------- ------------+--------------------+------------ ------------------+
|事件/资源类型 |资源类型 1 |资源类型 2 |资源类型 3 |资源类型 4 |资源类型 5 |
+---------------------+------------------------+-- ----------------------+---------------------------- ------------+--------------------+------------ ------------------+
|事件一 |资源 1、资源 2 |资源 3、资源 4 |资源 5、资源 6、资源 7 |资源 8 |空 |
|事件二 |空 |资源 3 |资源 11、资源 12、资源 13 |资源 14 |资源 1、资源 9、资源 16 |
+---------------------+------------------------+-- ----------------------+---------------------------- ------------+--------------------+------------ ------------------+

我知道如何在 ms-sql 中使用 PIVOT 语句，但我不知道如何将资源名称聚合为每种资源类型的逗号分隔项的串联。

附注
我还可以使用 SSRS 2008-R2 提供的 Martix 解决方案，使用 Report Builde 3，将第一个表作为我的数据集，并创建一个矩阵，将资源名称聚合为逗号分隔的字符串。

Answer 1

为了获得结果，首先您应该将值连接到逗号分隔的列表中。

我会用 CROSS APPLY和 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)

见 SQL Fiddle with Demo .给你结果:

| 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 |

然后您将申请您的 PIVOT对于这个结果:

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

见 SQL Fiddle with Demo .您的最终结果将是:

| 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 |