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 с первой таблицей в качестве набора данных и создать матрицу, которая будет объединять имена ресурсов в строку, разделенную запятыми.


sql tsql reporting-services pivot reportbuilder3.0
person Mortalus    schedule 09.02.2013    source источник
comment
stackoverflow.com/questions/10294846/ См. этот ответ: FOR XML PATH   -  person Paul    schedule 09.02.2013
comment
Доступна агрегатная функция CLR Concate, которую вы можете добавить в свою базу данных. Я полагаю, что тогда вы сможете легко сделать поворот, чтобы сделать это, поскольку вы просто использовали бы обычный поворот, но вместо того, где вы использовали бы совокупную СУММУ/МАКС/и т. д. вы бы использовали агрегатную функцию Concatenate. Здесь есть пример совокупной конкатенации, есть и другие в Интернете: msdn.microsoft. com/en-us/library/ms182741.aspx Это действительно полезная функция.   -  person AaronLS    schedule 09.02.2013
comment
@AaronLS Спасибо .. это действительно нас полно, но, к сожалению, я не мог создавать агрегаты, поскольку клиент может выполнять только простые запросы на выборку, и он никогда не получит разрешения на создание нового агрегата CLR ..   -  person Mortalus    schedule 09.02.2013
comment
@Paul Как мне повернуть приведенную выше таблицу, используя предоставленный вами ответ?   -  person Mortalus    schedule 09.02.2013
comment
@Mortalus - ответ опубликован с использованием примера комментария   -  person Paul    schedule 09.02.2013

Ответы (4)


arrow_upward
20
arrow_downward

Чтобы получить результат, сначала вы должны объединить значения в список, разделенный запятыми.

Я бы использовал 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 с демонстрацией. Это дает вам результат:

| 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 с демонстрацией. Тогда ваш окончательный результат будет:

| 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 |
person Taryn    schedule 09.02.2013

arrow_upward
4
arrow_downward

Это работает для меня в 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;
person Paul    schedule 09.02.2013

arrow_upward
2
arrow_downward

В построителе отчетов вы должны использовать мастер Таблица или Матрица и сделать следующее:

  • Resource Type в качестве групп столбцов.
  • Event Name в качестве групп строк.
  • И Resource Name в качестве деталей, вам придется использовать функцию агрегирования, такую ​​как 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"), ", ")

Проверено и протестировано:

введите здесь описание изображения

person glh    schedule 09.02.2013

arrow_upward
0
arrow_downward

Полный рабочий пример:

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
person gotqn    schedule 09.02.2013