Sql PIVOT и совокупность конкатенаций строк
Я хотел бы использовать сводный SQL-запрос для построения таблицы результатов, в которой текст конкатенации возникает в разделе DATA сводной таблицы.
т.е. я получаю следующий результат от использования простого выбора:
+------------+-----------------+---------------+
| 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 |
+------------+-----------------+---------------+
И я хотел бы построить запрос результата, который будет выглядеть так:
+---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
| 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 |
+---------------------+------------------------+------------------------+---------------------------------------+-----------------+-------------------------------------+
Я знаю, как использовать инструкцию PIVOT в ms-sql, но я не знаю, как агрегировать имя ресурса в конкатенацию разделенных запятыми элементов для каждого типа ресурса.
PS Я мог бы также использовать решение, используя Martix, предоставленный SSRS 2008-R2, используя Report Builde 3 с первой таблицей в качестве моего набора данных и создайте матрицу, которая будет агрегировать имена ресурсов в строку, разделенную запятой.
Ответы
Ответ 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 |
Ответ 2
Это работает для меня в SQL 2008, и он динамический - будет обрабатывать дополнительный Resource Type
Рабочий SQLFiddle
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;
Ответ 3
В построителе отчетов вы должны использовать мастер таблицы или матрицы и выполнить следующие действия:
- Поле "
Resource Type виде групп столбцов. - Поле "
Event Name виде групп строк. - В поле "
Resource Name качестве деталей вам нужно будет использовать функцию aggregatre, такую как Count.
На этом этапе завершите работу мастера после завершения редактирования ячейки имени 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"), ", ")
Теперь попробовали и протестировали:
![enter image description here]()
Ответ 4
Полный рабочий пример:
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
