[SQL] 고유 기록에 PIVOT 쿼리

고유 기록에 PIVOT 쿼리

나는 테이블 아래에 있습니다 :

------------------------------------------------------
| Id    Code  percentage  name  name1   activity     |
-----------------------------------------------------
| 1   Prashant  43.43    James  James_  Running      |
| 1   Prashant  70.43    Sam    Sam_    Cooking      |
| 1   Prashant  90.34    Lisa   Lisa_   Walking      |
| 1   Prashant  0.00     James  James_  Stealing     |
| 1   Prashant  0.00     James  James_  Lacking      |
| 1   Prashant  73       Sam     Sam_   Cooking 1    |
------------------------------------------------------

문제는 부족의 MAX 기능으로는 열 이름 이름 1과 0.00 값을 무시 때문이다

예상 결과:

-------------------------------------------------------------------
Id  Code        James    James_  Sam        Sam_    Lisa      Lisa_
-------------------------------------------------------------------
1   Prashant    Running  43.43  Cooking     3.43    Walking   90.34
1   Prashant    Stealing 0.0    Cooking 1   73      NULL      NULL
1   Prashant    Lacking  0.0    NULL        NULL    NULL      NULL
-------------------------------------------------------------------

내가 뭘하려 피벗 검색어 :

DECLARE @DynamicPivotQuery NVARCHAR(MAX)

SET @DynamicPivotQuery  = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
    INTO ##TempPivot 
    FROM A
    PIVOT(MAX(activity)
          FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
          (
          MAX(percentage)
          FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'


EXECUTE(@DynamicPivotQuery) 

SELECT * 
INTO #RESULT 
FROM ##TempPivot


SELECT * 
FROM #RESULT

샘플 쿼리는 데이터를 생성합니다 :

CREATE TABLE A
(
  Id NVARCHAR(10),
  Code NVARCHAR(MAX),
  percentage NVARCHAR(MAX),
  name NVARCHAR(MAX),
  name1 NVARCHAR(MAX),
  activity NVARCHAR(MAX)
)


INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')

해결법

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

   1.당신이 혼합으로 ()는 ROW_NUMBER를 추가 할 경우, 피벗 활동과 비율 사이의 관계를 유지할 수있을 것입니다.

   당신이 혼합으로 ()는 ROW_NUMBER를 추가 할 경우, 피벗 활동과 비율 사이의 관계를 유지할 수있을 것입니다.

   ;with cte as 
   (
       select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
       from A
   ),
   cte2 as
   (
       SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
       FROM cte
       PIVOT(MAX(activity)
             FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
             (
             MAX(percentage)
             FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
   )
   select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
   from cte2
   group by Id, Code, ROWNUM
   

   보고:

   Id  Code        James       James_  Sam         Sam_    Lisa    Lisa_
   1   Prashant    Running     43.43   Cooking 1   73      Walking 90.34
   1   Prashant    Stealing    0.00    Cooking     3.43    NULL    NULL
   1   Prashant    Lacking     0.00    NULL        NULL    NULL    NULL
   

   아이디어는 첫 번째 공통 테이블 표현식에, 당신은이에는 A 테이블을 변환입니다 :

   Id  Code        percentage  name    name1   activity    ROWNUM
   1   Prashant    43.43       James   James_  Running     1
   1   Prashant    0.00        James   James_  Stealing    2
   1   Prashant    0.00        James   James_  Lacking     3
   1   Prashant    90.34       Lisa    Lisa_   Walking     1
   1   Prashant    73          Sam     Sam_    Cooking 1   1
   1   Prashant    3.43        Sam     Sam_    Cooking     2
   

   그리고 나머지 쿼리 전체에서 ROWNUM 열은 활동에 백분율 값을 바인딩하는 역할을합니다.

   당신이 작업 쿼리를 일단 그것을 만들기 동적 쉽습니다. 그냥 변수를 모든 동적 부품 (이름이 경우, 쉼표로 구분 된 목록을 마우스 오른쪽?) 대체합니다. 이 같은:

   declare @sql nvarchar(max)
   declare @name_concat nvarchar(max)
   declare @name1_concat nvarchar(max)
   declare @select_aggs nvarchar(max)
   select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
   select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
   
   ;with cte_all_names as (
       select name from A
       union all 
       select name1 from A
   )
   select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
   
   select @sql = '
   ;with cte as 
   (
       select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
       from A
   ),
   cte2 as
   (
       SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
       FROM cte
       PIVOT(MAX(activity)
             FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
             (
             MAX(percentage)
             FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
   )
   select Id, Code, ' + @select_aggs + '
   from cte2
   group by Id, Code, ROWNUM
   '
   
   exec sp_executesql @sql
   

from https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records by cc-by-sa and MIT license