복붙노트

[SQL] SQL PIVOT 및 문자열 연결 집계

SQL

SQL PIVOT 및 문자열 연결 집계

나는 결과 테이블을 구성하는 피벗 SQL 쿼리를 사용하고자하는 경우 피벗 테이블의 데이터 섹션 내 결과로 CONCATENATE 텍스트입니다.

즉 나는 것은 간단한 선택을 사용하는 결과는 다음이있다 :

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

나는 MS-SQL에서 PIVOT 문을 사용하지만 난 쉼표의 연결에 자원 이름을 집계하는 방법을 알고 각 자원 유형에 대한 항목을 구분하지 않는 방법을 알고있다.

추신 나는 또한 내 데이터 세트로 첫 번째 테이블 보고서 Builde 3를 사용하여 SSRS 2008 R2에서 제공하는 Martix를 사용하여 솔루션을 사용하고 쉼표로 리소스 이름을 집계하는 행렬을 작성 문자열을 분리 할 수있다.

해결법

  1. ==============================

    1.결과를 얻기 위해서는 먼저 쉼표로 구분 된 목록에 값을 연결합니다.

    결과를 얻기 위해서는 먼저 쉼표로 구분 된 목록에 값을 연결합니다.

    나는 CROSS이 적용 및 XML 경로를 사용합니다 :

    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 바이올린을 참조하십시오. 는 결과를 제공합니다 :

    | 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 바이올린을 참조하십시오. 귀하의 최종 결과는있을 것입니다 :

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

    2.이것은 SQL 2008에서 나를 위해 작동하고, 그것의 동적 - 추가 자원 유형 처리합니다

    이것은 SQL 2008에서 나를 위해 작동하고, 그것의 동적 - 추가 자원 유형 처리합니다

    작업 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. ==============================

    3.보고서에서 당신은 테이블 또는 행렬 마법사를 사용하여 다음을 수행해야 빌더 :

    보고서에서 당신은 테이블 또는 행렬 마법사를 사용하여 다음을 수행해야 빌더 :

    이 단계에서 완전한 편집하면 다음 식으로 자원 이름 셀을 마법사를 완료합니다. 식을 교체 :

    =Join( LookupSet( Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
                  Fields!EVENT_NAME.Value + Fields!RESOURCE_TYPE.Value,
                  Fields!RESOURCE_NAME.Value, "DataSet1"), ", ")

    지금 시도하고 테스트 :

  4. ==============================

    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

    5. from https://stackoverflow.com/questions/14783680/sql-pivot-and-string-concatenation-aggregate by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 오라클에서 열 행을 변환하는? [복제]  (0) 2020.04.26
[SQL] 왜 UDF 너무 느린 서브 쿼리보다?  (0) 2020.04.26
[SQL] MySQL은 # 1093 - 당신은 FROM 절에서 업데이트 대상 테이블 '경품'을 지정할 수 없습니다  (0) 2020.04.26
[SQL] 서브 타입의 참조 무결성을 구현하는 방법  (0) 2020.04.26
[SQL] 엔티티 프레임 워크 - 저장 프로 시저의 반환 값  (0) 2020.04.26
,

티스토리툴바