T-SQL의 피벗 함수 이해

T-SQL의 피벗 함수 이해

저는 SQL이 매우 생소합니다.

다음과 같은 테이블이 있습니다.

아이디	팀 ID	사용자 ID	요소 ID	단계 ID	노력
1	1	1	3	5	6.74
2	1	1	3	6	8.25
3	1	1	4	1	2.23
4	1	1	4	5	6.8
5	1	1	4	6	1.5

그리고 저는 이런 자료를 얻으라고 들었습니다.

요소 ID	단계 ID1	ID5 단계	ID6 단계
3	NULL	6.74	8.25
4	2.23	6.8	1.5

피벗 기능을 사용해야 한다는 것을 이해합니다.하지만 그것을 명확하게 이해할 수 없습니다.위의 경우 누군가 설명해주시면 큰 도움이 될 것 같습니다.(또는 대안이 있는 경우)

A은(는) 하나의 열에서 여러 열로 데이터를 회전시키는 데 사용됩니다.

예를 들어, 다음은 회전할 열을 하드 코딩하는 것을 의미하는 정적 피벗입니다.

create table temp
(
  id int,
  teamid int,
  userid int,
  elementid int,
  phaseid int,
  effort decimal(10, 5)
)

insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)

select elementid
  , [1] as phaseid1
  , [5] as phaseid5
  , [6] as phaseid6
from
(
  select elementid, phaseid, effort
  from temp
) x
pivot
(
  max(effort)
  for phaseid in([1], [5], [6])
)p

작동 중인 버전의 SQL 데모입니다.

열 목록을 동적으로 만들고 피벗을 수행하는 동적 피벗을 통해서도 이 작업을 수행할 수 있습니다.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT elementid, ' + @cols + ' from 
            (
                select elementid, phaseid, effort
                from temp
           ) x
            pivot 
            (
                 max(effort)
                for phaseid in (' + @cols + ')
            ) p '


execute(@query)

두 가지 모두에 대한 결과:

ELEMENTID   PHASEID1    PHASEID5    PHASEID6
3           Null        6.74        8.25
4           2.23        6.8         1.5

이것들은 매우 기본적인 피벗 예제입니다. 이것들을 살펴보시기 바랍니다.

SQL SERVER – 피벗 및 UNPIVOT 표 예제

제품 테이블에 대한 위 링크의 예:

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
 PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

렌더링:

 PRODUCT FRED  KATE
 --------------------
 BEER     24    12
 MILK      3     1
 SODA   NULL     6
 VEG    NULL     5

유사한 예는 SQL Server의 블로그 게시물 피벗 테이블에서 확인할 수 있습니다. 간단한 샘플

여기에 아무도 언급하지 않은 추가할 것이 있습니다.

그pivot함수는 소스에 3개의 열이 있을 때 잘 작동합니다.하나는 더를 위한 것입니다.aggregate열로 펼 수 있는 하나for그리고 하나는 피벗으로row분배.제품 예제에서는 다음과 같습니다.QTY, CUST, PRODUCT.

그러나 원본에 더 많은 열이 있는 경우 추가 열당 고유한 값을 기준으로 피벗당 하나의 행이 아닌 여러 행으로 결과가 나뉩니다.Group By단순 쿼리로 수행).

다음 예를 참조하십시오. 소스 테이블에 타임스탬프 열을 추가했습니다.

이제 그 영향을 확인해 보십시오.

SELECT CUST, MILK

FROM Product
-- FROM (SELECT CUST, Product, QTY FROM PRODUCT) p
PIVOT (
    SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt

ORDER BY CUST

---

이 문제를 해결하려면 위에서 모든 사람이 수행한 것처럼 하위 쿼리를 소스로 풀 수 있습니다. 단 3개의 열만 사용하면 됩니다. (이것은 시나리오에 항상 효과가 있는 것은 아닙니다. 만약 당신이 다음을 입력해야 한다면 상상해 보십시오.where타임스탬프의 조건).

두 번째 해결책은 다음을 사용하는 것은group by피벗된 열 값의 합계를 다시 수행합니다.

SELECT 
CUST, 
sum(MILK) t_MILK

FROM Product
PIVOT (
    SUM(QTY) FOR PRODUCT IN (MILK)
) AS pvt

GROUP BY CUST
ORDER BY CUST

GO

피벗은 데이터 집합의 열 중 하나를 행에서 열로 변환하는 데 사용됩니다(일반적으로 이 열을 산포 열이라고 함).당신이 제시한 예에서, 이것은 다음을 변환하는 것을 의미합니다.PhaseID열 집합에 대한 행. 각 고유한 값에 대해 하나의 열이 있습니다.PhaseID이 경우 - 1, 5 및 6을 포함할 수 있습니다.

이러한 피벗 값은 다음을 통해 그룹화됩니다.ElementID열을 입력합니다.

일반적으로 산포 값의 교차점에서 참조되는 값을 제공하는 어떤 형태의 집계도 제공해야 합니다.PhaseID) 및 그룹화 값(ElementID). 주어진 예에서 사용될 집계는 불분명하지만, 다음과 관련이 있습니다.Effort기둥.

이 피벗이 완료되면 그룹화 및 확산 열을 사용하여 집계 값을 찾습니다.아니면 당신의 경우에는ElementID그리고.PhaseIDX 다찾Effort.

그룹화, 확산, 집계 용어를 사용하면 일반적으로 피벗에 대한 예제 구문을 볼 수 있습니다.

WITH PivotData AS
(
    SELECT <grouping column>
        , <spreading column>
        , <aggregation column>
    FROM <source table>
)
SELECT <grouping column>, <distinct spreading values>
FROM PivotData
    PIVOT (<aggregation function>(<aggregation column>)
        FOR <spreading column> IN <distinct spreading values>));

여기에서는 그룹화, 확산 및 집계 열이 소스에서 피벗 테이블로 변환되는 방법에 대한 그래픽 설명을 제공합니다.

    SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

    DaysToManufacture          AverageCost
0                          5.0885
1                          223.88
2                          359.1082
4                          949.4105

    -- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;




Here is the result set.
Cost_Sorted_By_Production_Days    0         1         2           3       4       
AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

호환성 오류를 설정하려면

피벗 기능을 사용하기 전에 이 기능을 사용합니다.

ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100  

FOR XML PATH는 Microsoft Azure Synapse Server에서 작동하지 않을 수 있습니다.가능한 대안은 @Taryn 동적 생성 콜 접근법에 따라 STRING_AGG를 사용하여 동일한 결과를 얻습니다.

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

SELECT @cols = STRING_AGG(QUOTENAME(c.phaseid),', ')
/*OPTIONAL: within group (order by cast(t1.[FLOW_SP_SLPM] as INT) asc)*/
FROM (SELECT phaseid FROM temp
GROUP BY phaseid) c

set @query = 'SELECT elementid,' + @cols + ' from 
             (
                select elementid,
                phaseid,
                effort
                from temp
            ) x
            PIVOT 
            (
                max(effort)
                for phaseid in (' + @cols + ')
            ) p '

execute(@query)

언급URL : https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql