PIVOT 절
적용 대상: 
Databricks SQL Databricks 런타임
지정된 열 목록의 고유한 값을 별도의 열로 회전하여 table_reference의 행을 변환합니다.
구문
table_reference PIVOT ( { aggregate_expression [ [ AS ] agg_column_alias ] } [, ...]
FOR column_list IN ( expression_list ) )
column_list
{ column_name |
( column_name [, ...] ) }
expression_list
{ expression [ AS ] [ column_alias ] |
{ ( expression [, ...] ) [ AS ] [ column_alias] } [, ...] ) }
매개 변수
-
PIVOT작업의 주체를 식별합니다. -
모든 열 참조
table_reference가 집계 함수에 대한 인수인 모든 형식의 식입니다. -
집계 결과에 대한 선택적 별칭입니다. 별칭이 지정되지 않은 경우
PIVOT은aggregate_expression을 기반으로 별칭을 생성합니다. column_list
회전할 열 집합입니다.
-
table_reference의 열입니다.
-
expression_list
column_list에서 열 별칭으로 값을 매핑합니다.-
최소 공통 형식을 해당
column_name과 공유하는 형식이 있는 리터럴 식입니다.각 튜플의 식 수는
column_list에서column_names의 수와 일치해야 합니다. -
생성된 열의 이름을 지정하는 선택적 별칭입니다. 별칭이 지정되지 않은 경우
PIVOT은expression을 기반으로 별칭을 생성합니다.
-
결과
다음 형식의 임시 테이블입니다.
aggregate_expression또는column_list에 지정되지 않은table_reference의 중간 결과 집합에 있는 모든 열입니다.이러한 열은 그룹화 열입니다.
각
expression튜플과aggregate_expression조합의 경우PIVOT은 하나의 열을 생성합니다. 형식은aggregate_expression의 형식입니다.하나의
aggregate_expression만 있는 경우 열은column_alias를 사용하여 이름을 지정합니다. 그렇지 않으면column_alias_agg_column_alias로 이름이 지정됩니다.각 셀의 값은
FILTER ( WHERE column_list IN (expression, ...)을 사용하는aggregation_expression의 결과입니다.
예제
-- A very basic PIVOT
-- Given a table with sales by quarter, return a table that returns sales across quarters per year.
> CREATE TEMP VIEW sales(year, quarter, region, sales) AS
VALUES (2018, 1, 'east', 100),
(2018, 2, 'east', 20),
(2018, 3, 'east', 40),
(2018, 4, 'east', 40),
(2019, 1, 'east', 120),
(2019, 2, 'east', 110),
(2019, 3, 'east', 80),
(2019, 4, 'east', 60),
(2018, 1, 'west', 105),
(2018, 2, 'west', 25),
(2018, 3, 'west', 45),
(2018, 4, 'west', 45),
(2019, 1, 'west', 125),
(2019, 2, 'west', 115),
(2019, 3, 'west', 85),
(2019, 4, 'west', 65);
> SELECT year, region, q1, q2, q3, q4
FROM sales
PIVOT (sum(sales) AS sales
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
2018 east 100 20 40 40
2019 east 120 110 80 60
2018 west 105 25 45 45
2019 west 125 115 85 65
-- The same query written without PIVOT
> SELECT year, region,
sum(sales) FILTER(WHERE quarter = 1) AS q1,
sum(sales) FILTER(WHERE quarter = 2) AS q2,
sum(sales) FILTER(WHERE quarter = 3) AS q2,
sum(sales) FILTER(WHERE quarter = 4) AS q4
FROM sales
GROUP BY year, region;
2018 east 100 20 40 40
2019 east 120 110 80 60
2018 west 105 25 45 45
2019 west 125 115 85 65
-- Also PIVOT on region
> SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
FROM sales
PIVOT (sum(sales) AS sales
FOR (quarter, region)
IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
(3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));
2018 100 105 20 25 40 45 40 45
2019 120 125 110 115 80 85 60 65
-- The same query written without PIVOT
> SELECT year,
sum(sales) FILTER(WHERE (quarter, region) IN ((1, 'east'))) AS q1_east,
sum(sales) FILTER(WHERE (quarter, region) IN ((1, 'west'))) AS q1_west,
sum(sales) FILTER(WHERE (quarter, region) IN ((2, 'east'))) AS q2_east,
sum(sales) FILTER(WHERE (quarter, region) IN ((2, 'west'))) AS q2_west,
sum(sales) FILTER(WHERE (quarter, region) IN ((3, 'east'))) AS q3_east,
sum(sales) FILTER(WHERE (quarter, region) IN ((3, 'west'))) AS q3_west,
sum(sales) FILTER(WHERE (quarter, region) IN ((4, 'east'))) AS q4_east,
sum(sales) FILTER(WHERE (quarter, region) IN ((4, 'west'))) AS q4_west
FROM sales
GROUP BY year, region;
2018 100 105 20 25 40 45 40 45
2019 120 125 110 115 80 85 60 65
-- To aggregate across regions the column must be removed from the input.
> SELECT year, q1, q2, q3, q4
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (sum(sales) AS sales
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
2018 205 45 85 85
2019 245 225 165 125
-- The same query without PIVOT
> SELECT year,
sum(sales) FILTER(WHERE quarter = 1) AS q1,
sum(sales) FILTER(WHERE quarter = 2) AS q2,
sum(sales) FILTER(WHERE quarter = 3) AS q3,
sum(sales) FILTER(WHERE quarter = 4) AS q4
FROM sales
GROUP BY year;
-- A PIVOT with multiple aggregations
> SELECT year, q1_total, q1_avg, q2_total, q2_avg, q3_total, q3_avg, q4_total, q4_avg
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (sum(sales) AS total, avg(sales) AS avg
FOR quarter
IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
2018 205 102.5 45 22.5 85 42.5 85 42.5
2019 245 122.5 225 112.5 165 82.5 125 62.5
-- The same query without PIVOT
> SELECT year,
sum(sales) FILTER(WHERE quarter = 1) AS q1_total,
avg(sales) FILTER(WHERE quarter = 1) AS q1_avg,
sum(sales) FILTER(WHERE quarter = 2) AS q2_total,
avg(sales) FILTER(WHERE quarter = 2) AS q2_avg,
sum(sales) FILTER(WHERE quarter = 3) AS q3_total,
avg(sales) FILTER(WHERE quarter = 3) AS q3_avg,
sum(sales) FILTER(WHERE quarter = 4) AS q4_total,
avg(sales) FILTER(WHERE quarter = 4) AS q4_avg
FROM sales
GROUP BY year;
> CREATE TEMP VIEW person (id, name, age, class, address) AS
VALUES (100, 'John', 30, 1, 'Street 1'),
(200, 'Mary', NULL, 1, 'Street 2'),
(300, 'Mike', 80, 3, 'Street 3'),
(400, 'Dan', 50, 4, 'Street 4');
2018 205 102.5 45 22.5 85 42.5 85 42.5
2019 245 122.5 225 112.5 165 82.5 125 62.5