저는 SQL을 처음 접했습니다.
다음과 같은 테이블이 있습니다.
ID | TeamID | UserID | ElementID | PhaseID | Effort
-----------------------------------------------------
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
그리고 나는 이와 같은 데이터를 얻으라고 들었습니다.
ElementID | PhaseID1 | PhaseID5 | PhaseID6
--------------------------------------------
3 | NULL | 6.74 | 8.25
4 | 2.23 | 6.8 | 1.5
PIVOT 기능을 사용해야 함을 이해합니다. 그러나 그것을 명확하게 이해할 수 없습니다. 누군가가 위의 경우에 설명 할 수 있다면 큰 도움이 될 것입니다. (또는 대안이 있다면)
답변
PIVOT한 열의 데이터를 여러 열로 회전 하는 데 사용됩니다.
예를 들어 다음은 회전하려는 열을 하드 코딩하는 것을 의미하는 STATIC Pivot입니다.
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 데모 입니다.
열 목록을 동적으로 생성하고 PIVOT을 수행하는 동적 PIVOT을 통해이 작업을 수행 할 수도 있습니다.
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 – PIVOT 및 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간단한 쿼리에서 와 같이 ).
이 예제를 참조하십시오. ive는 소스 테이블에 타임 스탬프 열을 추가했습니다.
이제 그 영향을 확인하십시오.
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. 타임 스탬프 에 대한 조건 을 입력해야한다고 상상해보세요 ).
두 번째 해결책은 a를 사용 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 .
일반적으로 분산 값 ( )과 그룹화 값 ( ) 의 교차점에서 참조하는 값을 제공 하는 집계 형식도 제공해야합니다 . 주어진 예에서 사용될 집계 는 명확하지 않지만 열을 포함합니다 .PhaseIDElementIDEffort
이 피벗이 완료되면 그룹화 및 분산 열 을 사용하여 집계 값 을 찾습니다 . 또는 귀하의 경우에서 ElementID와 PhaseIDX조회 Effort.
은 Using 집계, 확산, 그룹화 용어는 일반적으로 피벗 등을위한 예 구문을 볼 수 있습니다 :
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>));
이는 그룹화, 분산 및 집계 열이 소스에서 피벗 된 테이블로 변환 하는 방법에 대한 그래픽 설명을 제공합니다 .
답변
호환성 오류를 설정하려면
피벗 기능을 사용하기 전에 이것을 사용하십시오
ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100
답변
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
답변
