SQL PIVOT和字符串串联聚合 | PIVOT和字符串串联聚合

本文介绍了SQL PIVOT和字符串串联聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

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

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我还可以使用使用SSRS 2008-R2提供的Martix的解决方案，并使用Report Builde 3，其中第一个表作为数据集，并创建一个矩阵，将资源名称聚合为逗号分隔的字符串.

P.SI 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.

推荐答案

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

In order to get the result, first you should concatenate the values into the comma separated list.

我会使用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小提琴.为您提供结果:

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 |

然后，您将PIVOT应用于此结果:

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

请参见带有演示的SQL小提琴.您的最终结果将是:

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 |

这篇关于SQL PIVOT和字符串串联聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..