Pivot 함수와 Pivot XML

Oracle 11g에서 나온 신기술(New Feature)로써 열과 행을 바꿔서 보여줄 수 있는 기능입니다.

티베로6기준으로는 Pivot 기능은 가능하지만 PivotXML은 구현되지 않습니다.

Pivot함수는 내부적으로 그룹핑된 결과를 제공합니다. 그렇기 때문에 PIVOT절에서 그룹핑된 결과를 정의해야 하는데, GROUP BY가 들어간 쿼리와 같다고 생각하면 됩니다.

 

테스트 테이블생성

create table bae_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));

 

테스트 데이터 삽입

insert into bae_pivot_test values (1,10.99,sysdate-10,'Toy');
insert into bae_pivot_test values (1,10.99,sysdate-9,'Toy');
insert into bae_pivot_test values (1,20.999,sysdate-9,'Toy');
insert into bae_pivot_test values (1,20.999,sysdate-9,'Tool');
insert into bae_pivot_test values (1,20.999,sysdate-9,'Furniture');
insert into bae_pivot_test values (1,20.999,sysdate-9,'Food');
insert into bae_pivot_test values (1,5.00,sysdate-8,'Toy');
insert into bae_pivot_test values (1,5.00,sysdate-8,'Tool');
insert into bae_pivot_test values (1,5.00,sysdate-8,'Furniture');
insert into bae_pivot_test values (1,5.00,sysdate-8,'Food');
insert into bae_pivot_test values (1,5.00,sysdate-7,'Toy');
insert into bae_pivot_test values (1,5.00,sysdate-7,'Tool');
insert into bae_pivot_test values (1,5.00,sysdate-7,'Furniture');
insert into bae_pivot_test values (1,5.00,sysdate-7,'Food');

 

commit;

 

TEST@ysbae> select * from bae_pivot_test;

        ID       COST TX_DATE   PRODUCT_TYPE
---------- ---------- --------- --------------------------------------------------
         1      10.99 19-AUG-20 Toy
         1      10.99 20-AUG-20 Toy
         1     20.999 20-AUG-20 Toy
         1     20.999 20-AUG-20 Tool
         1     20.999 20-AUG-20 Furniture
         1     20.999 20-AUG-20 Food
         1          5 21-AUG-20 Toy
         1          5 21-AUG-20 Tool
         1          5 21-AUG-20 Furniture
         1          5 21-AUG-20 Food
         1          5 22-AUG-20 Toy

        ID       COST TX_DATE   PRODUCT_TYPE
---------- ---------- --------- --------------------------------------------------
         1          5 22-AUG-20 Tool
         1          5 22-AUG-20 Furniture
         1          5 22-AUG-20 Food

 

Product Type이 Tool와 Furniture인 컬럼을 열로 만드는 피벗테이블을 생성해보도록 하겠습니다.

 

TEST@ysbae> select * from
(select trunc(tx_date,'dd') DAY, product_type,cost from bae_pivot_test)
pivot (SUM(cost) for product_type in ('Tool','Furniture')); 

 

DAY           'Tool' 'Furniture'
--------- ---------- -----------
19-AUG-20 (null)     (null)
22-AUG-20          5           5
20-AUG-20     20.999      20.999
21-AUG-20          5           5

4 rows selected.

 

from 절 밑에 select 쿼리에서 필요한 정보의 쿼리를 작성 후 pivot에서 group by 해줄 컬럼을 선택하고 기준을 선택하면 됩니다. 단점은 pivot in절에는 값을 명시적으로 적어줘야 한다는 것입니다.  위의 쿼리에서는 ('Tool','Furniture')와 같이 필터링할 컬럼은 명시적으로 선택해야합니다.

 

in 절안에 서브쿼리는 XML 키워드와 함께 사용할 수 있습니다. 출력은 위의 쿼리처럼 나오지 않고 XML 형식의 컬럼값으로 나오게 됩니다. 서브쿼리값은 고유한값이 결과값이 나오게 짜야합니다. 쿼리가 고유한 값을 반환하는지 확실하지 않은 경우는 아래의 키워드를 사용합니다.

 

ANY

XML에서만 사용할 수 있습니다. 와일드 카드 역할을 하며 각 행의 XML 문자열은 해당 행의 암시적인 GROUP BY 값에 해당하는 데이터를 보유합니다.

 

XML 쿼리와 ANY 키워드를 함께 사용한 쿼리입니다.

TEST@ysbae> set long 10000000
TEST@ysbae> SELECT * FROM
(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bae_pivot_test)
PIVOT XML(SUM(cost) as cost FOR product_type IN (ANY)); 

DAY
---------
PRODUCT_TYPE_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">10.99</column></item></PivotSet>

20-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">20.999</column></item
><item><column name = "PRODUCT_TYPE">Tool</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">31.989</column></item></PivotSet>

21-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">5</column></item><item><co

DAY
---------
PRODUCT_TYPE_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lumn name = "PRODUCT_TYPE">Tool</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">5</column></item></PivotSet>

22-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">5</column></item><item><co
lumn name = "PRODUCT_TYPE">Tool</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">5</column></item></PivotSet>


4 rows selected.

 

ANY 키워드뿐만 아니라 서브쿼리가 들어가도 사용할 수 있습니다.

product_type이 들어있는 새로운 테이블을 생성합니다.

 

create table product_name (name varchar(20));

insert into product_name values ('Toy');
insert into product_name values ('Tool');
insert into product_name values ('Furniture');
insert into product_name values ('Food');

commit;

 

TEST@ysbae> select * from product_name;

NAME
--------------------
Toy
Tool
Furniture
Food

 

TEST@ysbae>  SELECT * FROM
(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bae_pivot_test)
PIVOT XML(SUM(cost) as cost FOR product_type IN (select name from product_name));   2  

DAY
---------
PRODUCT_TYPE_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST"></column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST"></column></item><item><colu
mn name = "PRODUCT_TYPE">Tool</column><column name = "COST"></column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">10.99</column></item></PivotSet>

20-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">20.999</column></item
><item><column name = "PRODUCT_TYPE">Tool</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">31.989</column></item></PivotSet>

21-AUG-20

DAY
---------
PRODUCT_TYPE_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">5</column></item><item><co
lumn name = "PRODUCT_TYPE">Tool</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">5</column></item></PivotSet>

22-AUG-20
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">5</column></item><item><co
lumn name = "PRODUCT_TYPE">Tool</column><column name = "COST">5</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">5</column></item></PivotSet>


4 rows selected.