Sql PIVOT 和字符串连接聚合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14783680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql PIVOT and string concatenation aggregate
提问by Mortalus
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.
我想使用一个数据透视 SQL 查询来构建一个结果表，其中连接文本作为数据透视表的数据部分中的结果。
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 | +---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
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.
我知道如何在 ms-sql 中使用 PIVOT 语句，但我不知道如何将资源名称聚合为每种资源类型的逗号分隔项的串联。
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.
PS 我还可以使用 SSRS 2008-R2 提供的 Martix 解决方案，使用 Report Builde 3，将第一个表作为我的数据集，并创建一个矩阵，将资源名称聚合为逗号分隔的字符串。
采纳答案by Taryn
In order to get the result, first you should concatenate the values into the comma separated list.
为了获得结果，首先您应该将值连接到逗号分隔的列表中。
I would use CROSS APPLYand FOR XML PATH:
我会使用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)
See SQL Fiddle with Demo. The gives you result:
请参阅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 |
Then you will apply your PIVOTto this result:
然后你将你的应用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
See SQL Fiddle with Demo. Your final result will then be:
请参阅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 |
回答by Paul
This works for me in SQL 2008, and it's dynamic - will handle additional Resource Type
这在 SQL 2008 中对我有用，而且它是动态的 - 将处理额外的 Resource Type
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
GO
CREATE TABLE #test
(
eventName VARCHAR(30),
resourceType VARCHAR(30),
resourceName VARCHAR(30)
);
INSERT INTO #test
VALUES ('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;
回答by glh
In report builder you should use the Tableor Matrixwizard and do the following:
在报表生成器中，您应该使用表或矩阵向导并执行以下操作：
Resource Typefield as the column groups.Event Namefield as the row groups.- And
Resource Namefield as details, you willhave to use an aggregatre function such asCount.
Resource Type字段作为列组。Event Name字段作为行组。- 和
Resource Name字段作为详细信息，您将不得不使用聚合函数，例如Count.
At this stage complete the wizard then once complete edit the Resource Namecell as an expression. Replace the expression with:
在此阶段完成向导，然后完成将Resource Name单元格编辑为表达式。将表达式替换为：
=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:
现在尝试和测试：
回答by gotqn
Full working example:
完整的工作示例：
SET NOCOUNT ON
GO
DECLARE @SourceTable TABLE
(
EventName NVARCHAR(10)
,ResourceType NVARCHAR(20)
,ResourceName NVARCHAR(20)
)
INSERT INTO @SourceTable(EventName,ResourceType,ResourceName)
VALUES ('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')
;WITH SourceTable AS
(
SELECT DISTINCT ST1.EventName
,ST1.ResourceType
,(SELECT SUBSTRING((SELECT ',' +ResourceName
FROM @SourceTable AS ST2
WHERE ST1.EventName=ST2.EventName AND ST1.ResourceType=ST2.ResourceType
FOR XML PATH('')),2,200) AS CSV) AS ResourceName
FROM @SourceTable AS ST1
)
SELECT EventName
,[Resource Type 1]
,[Resource Type 2]
,[Resource Type 3]
,[Resource Type 4]
,[Resource Type 5]
FROM
(
SELECT EventName
,ResourceType
,ResourceName
FROM SourceTable
) PivotSource
PIVOT
(
MAX(ResourceName) FOR ResourceType IN ([Resource Type 1],[Resource Type 2],[Resource Type 3],[Resource Type 4],[Resource Type 5])
) PivotTable
SET NOCOUNT OFF
GO
