Oracle / PLSQL : PIVOT 절

이 Oracle 튜토리얼에서는 구문과 예제를 통해 Oracle PIVOT 절을 사용하는 방법을 설명합니다.

설명

Oracle PIVOT 절을 사용하면 Oracle 11g부터 교차 테이블 쿼리를 작성할 수 있습니다. 즉, 결과를 집계하고 행을 열로 회전할 수 있습니다.

구문

Oracle/PLSQL에서 PIVOT 절의 구문은 다음과 같습니다.

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

매개변수 및 인수

aggregate_function

SUM, COUNT, MIN, MAX, AVG 함수와 같은 함수일 수 있습니다.

IN ( expr1, expr2, ... expr_n )

교차 테이블 형식 쿼리 결과에서 column2를 피벗하여 제목으로 만들 값 목록입니다.

하위 쿼리

값 목록 대신 사용할 수 있습니다. 이 경우 하위 쿼리의 결과를 사용하여 교차 테이블 쿼리 결과에서 column2의 값을 피벗하여 제목으로 만들 수 있습니다.

적용 대상

PIVOT 절은 다음 버전의 Oracle/PLSQL에서 사용할 수 있습니다.

Oracle 12c, Oracle 11g

예제

Oracle에서 PIVOT 절을 사용하는 방법을 살펴보겠습니다.

이 예제에서는 다음과 같은 정의가 있는 orders라는 테이블을 기반으로 하겠습니다.

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

이 예제의 데이터를 표시하기 위해 다음 SELECT 문을 사용하여 orders 테이블에서 레코드를 선택합니다.

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

다음은 orders 테이블의 레코드입니다. 이 레코드를 사용하여 PIVOT 절이 어떻게 작동하는지 보여드리겠습니다.

order_id	customer_ref	product_id
50001	SMITH	10
50002	SMITH	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	SMITH	20
50008	SMITH	10
50009	SMITH	20

이제 다음 PIVOT 절을 사용하여 교차 테이블 쿼리를 만들어 보겠습니다.

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

이 예제에서 PIVOT 절은 다음과 같은 결과를 반환합니다.

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

이제 PIVOT 절을 분리하여 어떻게 작동하는지 설명해 보겠습니다.

포함할 필드 지정

먼저 교차 테이블에 어떤 필드를 포함할지 지정하겠습니다. 이 예제에서는 customer_ref 및 product_id 필드를 포함하려고 합니다. 이는 문에서 다음 부분에 의해 수행됩니다.

(
  SELECT customer_ref, product_id
  FROM orders
)

포함할 열을 원하는 순서대로 나열할 수 있습니다.

집계 함수 지정

다음으로 교차 테이블 쿼리를 만들 때 사용할 집계 함수를 지정해야 합니다. SUM, COUNT, MIN, MAX, AVG 함수와 같은 모든 집계를 사용할 수 있습니다.

이 예제에서는 COUNT 함수를 사용하겠습니다. 이 함수는 기준을 충족하는 product_id 값의 수를 계산합니다. 이는 문에서 다음 부분에 의해 수행됩니다.

PIVOT
(
  COUNT(product_id)

피벗 값 지정

마지막으로, 결과에 포함할 피벗 값을 지정해야 합니다. 피벗 값은 교차 테이블 쿼리에서 열 제목으로 사용됩니다. 괄호로 묶인 값 목록 또는 하위 쿼리를 사용하여 피벗 값을 지정할 수 있습니다.

이 예제에서는 다음과 같은 product_id 값만 반환합니다. 10, 20, 30. 이 값은 교차 테이블 쿼리에서 열 머리글이 됩니다. 또한 이러한 값은 product_id 값의 유한한 목록이며 가능한 모든 값을 반드시 포함하지는 않습니다.

이는 문에서 다음 부분에 의해 수행됩니다.

  FOR product_id IN (10, 20, 30)
)

이제 이 모든 것을 합치면 다음과 같은 피벗 테이블이 생깁니다.

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

< 이전 / 다음 : Data Types >