다중 행을 문자열로 변환
이것은 비교적 간단하여 || 또는 concat 함수를 사용하여 구현할 수 있습니다.
1 select concat(user_id, user_name) combined_str from user_profile
2
3 select user_id||user_name combined_str from user_profile
문자열을 다중 열로 변환
실제로는 문자열 분할 문제이며, substr, instr, regexp_substr 함수 방식을 사용할 수 있습니다.
문자열을 다중 행으로 변환
union all 함수 등을 사용합니다.
listagg 함수
1 select listagg(employee_name,' , ') within group (order by employee_name) from employee_table;
구문: listagg(열 이름, 구분자) within group (order by 열 이름)
wm_concat 함수
먼저 이 신기한 함수 wm_concat(열 이름)을 살펴보겠습니다. 이 함수는 열 값을 쉼표로 구분하여 한 줄로 표시할 수 있습니다. 다음으로 예제를 통해 이 신기한 함수가 어떻게 적용되는지 확인해 보겠습니다. 테스트 데이터를 준비합니다.
1 create table product_data(item_id number,item_name varchar2(20));
2
3 insert into product_data values(1,'Keyboard');
4 insert into product_data values(1,'Mouse');
5 insert into product_data values(1,'Monitor');
6 insert into product_data values(2,'Webcam');
7 insert into product_data values(2,'Headset');
효과 1: 행을 열로 변환, 기본적으로 쉼표로 구분
1 select wm_concat(item_name) item_names from product_data;
효과 2: 결과의 쉼표를 "|"로 교체
1 select replace(wm_concat(item_name),',','|') from product_data;
효과 3: ID별로 그룹화하여 item_name 병합
1 select item_id,wm_concat(item_name) item_names from product_data group by item_id;
SQL 문은 다음 SQL 문과 동일합니다
1 -------- 적용 범위: 8i,9i,10g 및 이후 버전 ( MAX + DECODE )
2 select item_id, max(decode(rn, 1, item_name, null)) || max(decode(rn, 2, ','||item_name, null)) || max(decode(rn, 3, ','||item_name, null)) combined_str
3 from (select item_id, item_name ,row_number() over(partition by item_id order by item_name) as rn from product_data) t group by item_id order by 1;
4
5 -------- 적용 범위: 8i,9i,10g 및 이후 버전 （ ROW_NUMBER + LEAD ）
6 select item_id, combined_str from (select item_id,row_number() over(partition by item_id order by item_name) as rn,item_name || lead(',' || item_name, 1)
7 over(partition by item_id order by item_name) || lead(',' || item_name, 2) over(partition by item_id order by item_name) || lead(',' || item_name, 3)
8 over(partition by item_id order by item_name) as combined_str from product_data) where rn = 1 order by 1;
9
10 -------- 적용 범위: 10g 및 이후 버전 （ MODEL ）
11 select item_id, substr(combined_str, 2) combined_str from product_data model return updated rows partition by(item_id) dimension by(row_number()
12 over(partition by item_id order by item_name) as rn) measures (cast(item_name as varchar2(20)) as combined_str) rules upsert iterate(3)
13 until(presentv(combined_str[iteration_number + 2], 1, 0)=0) (combined_str[0] = combined_str[0] || ',' || combined_str[iteration_number + 1]) order by 1;
14
15 -------- 적용 범위: 8i,9i,10g 및 이후 버전 （ MAX + DECODE ）
16 select t.item_id item_id, max(substr(sys_connect_by_path(t.item_name, ','), 2)) combined_str from (select item_id, item_name, row_number()
17 over(partition by item_id order by item_name) rn from product_data) t start with rn = 1 connect by rn = prior rn + 1 and item_id = prior item_id
18 group by t.item_id;
간편 확장 사용법:
사례: 뷰를 작성해야 하는데, "create or replace view as select 필드1,...필드50 from tablename"과 유사하며, 기본 테이블에 50개 이상의 필드가 있습니다. 수동으로 작성하면 너무 번거롭습니다. 더 간단한 방법이 있을까요? 물론 있습니다. wm_concat을 사용하여 이 요구 사항을 어떻게 간단하게 만드는지 보여드리겠습니다. APP_USER 테이블에 (id, username, password, age) 4개의 필드가 있다고 가정해 보겠습니다. 쿼리 결과는 다음과 같습니다
1 /** 여기서 테이블 이름은 기본적으로 대소문자를 구분합니다 */
2 select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sql_statement
3 from user_tab_columns where table_name='APP_USER';
시스템 테이블을 이용한 조회
1 select * from user_tab_columns
Oracle 11g 행과 열 전환 PIVOT 및 UNPIVOT 설명
Oracle 11g에서 Oracle은 pivot(행을 열로)과 unpivot(열을 행으로)이라는 2개의 쿼리를 추가했습니다.
참고: http://blog.csdn.net/tianlesoftware/article/details/7060306, http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
pivot 행을 열로 변환
테스트 데이터 (ID, 카테고리 이름, 판매 수량), 사례: 과일 유형별로 각 유형의 판매 수량이 표시되는 한 줄의 데이터를 쿼리합니다.
1 create table sales_data(id int,category_name varchar(20),quantity int); ---- 테이블 생성
2 insert into sales_data values(1, 'Apple', 1000);
3 insert into sales_data values(2, 'Apple', 2000);
4 insert into sales_data values(3, 'Apple', 4000);
5 insert into sales_data values(4, 'Orange', 5000);
6 insert into sales_data values(5, 'Orange', 3000);
7 insert into sales_data values(6, 'Grape', 3500);
8 insert into sales_data values(7, 'Mango', 4200);
9 insert into sales_data values(8, 'Mango', 5500);
그룹화 쿼리 (물론 이것은 한 줄의 데이터를 조회하는 요구 사항에 부합하지 않습니다)
1 select category_name, sum(quantity) total_quantity from sales_data group by category_name
행을 열로 변환하는 쿼리
1 select * from (select category_name, quantity from sales_data) pivot (sum(quantity) for category_name in ('Apple' Apple, 'Orange', 'Grape', 'Mango'));
참고: pivot(집계 함수 for 열 이름 in(유형)), 여기서 in('')에서 별칭을 지정할 수 있으며, in에는 서브쿼리도 지정할 수 있습니다. 예: select distinct code from customers
물론 pivot 함수를 사용하지 않아도 동일한 결과를 얻을 수 있지만, 코드가 길고 이해하기 쉽습니다
1 ------ 여러 서브쿼리
2 select * from （select sum(quantity) Apple from sales_data where category_name='Apple'）,（select sum(quantity) Orange from sales_data where category_name='Orange'）,
3 （select sum(quantity) Grape from sales_data where category_name='Grape'）,（select sum(quantity) Mango from sales_data where category_name='Mango'）;
4
5 ------ decode 함수 활용
6 select sum(decode(category_name,'Apple',quantity)) Apple, sum(decode(category_name,'Orange',quantity)) Orange,
7 sum(decode(category_name,'Grape',quantity)) Grape, sum(decode(category_name,'Mango',quantity)) Mango from sales_data
unpivot 열을 행으로 변환
명칭 그대로 여러 열을 하나의 열로 변환하는 것입니다.
사례: 현재 과일 테이블에는 4분기의 판매 수량이 기록되어 있으며, 이제 각 과일의 각 분기 판매 상황을 여러 행 데이터로 표시해야 합니다.
테이블 생성 및 데이터 삽입
1 create table fruit_sales(id int,fruit_name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
2
3 insert into fruit_sales values(1,'Apple',1000,2000,3300,5000);
4 insert into fruit_sales values(2,'Orange',3000,3000,3200,1500);
5 insert into fruit_sales values(3,'Banana',2500,3500,2200,2500);
6 insert into fruit_sales values(4,'Grape',1500,2500,1200,3500);
7 select * from fruit_sales
열을 행으로 변환하는 쿼리
1 select id , fruit_name, quarter, sales_amount from fruit_sales unpivot (sales_amount for quarter in (q1, q2, q3, q4) )
참고: unpivot에는 집계 함수가 없으며, sales_amount, quarter 필드도 임시 변수입니다
마찬가지로 unpivot을 사용하지 않아도 동일한 효과를 얻을 수 있지만 SQL 문장이 길고 실행 속도 효율도 전자보다 낮습니다
1 select id, fruit_name ,'Q1' quarter, (select q1 from fruit_sales where id=f.id) sales_amount from fruit_sales f
2 union
3 select id, fruit_name ,'Q2' quarter, (select q2 from fruit_sales where id=f.id) sales_amount from fruit_sales f
4 union
5 select id, fruit_name ,'Q3' quarter, (select q3 from fruit_sales where id=f.id) sales_amount from fruit_sales f
6 union
7 select id, fruit_name ,'Q4' quarter, (select q4 from fruit_sales where id=f.id) sales_amount from fruit_sales f
XML 유형
위 pivot 열을 행으로 변환 예제에서 이미 조회해야 할 유형이 무엇인지 알고 있으며, in() 방식으로 포함했습니다. 만약 어떤 값이 있는지 모른다면 어떻게 쿼리를 구축할 수 있을까요?
pivot 작업의 다른 하위 절 XML은 이 문제를 해결하는 데 사용될 수 있습니다. 이 하위 절은 XML 형식으로 pivot 작업의 출력을 생성하도록 허용하며, 이 출력에서 ANY라는 특별한 하위 절을 지정할 수 있습니다
다음과 같은 예시:
1 select * from (
2 select category_name, quantity as "Purchase Frequency"
3 from sales_data t
4 )
5 pivot xml (
6 sum(quantity) for category_name in (any)
7 )
보시다시피 CATEGORY_NAME_XML XMLTYPE 열에는 루트 요소가 있습니다. 각 값은 이름-값 요소 쌍으로 표시됩니다. 임의의 XML 분석기에서 출력을 사용하여 더 유용한 출력을 생성할 수 있습니다.
결론
Pivot은 SQL 언어에 매우 중요하고 실용적인 기능을 추가합니다. pivot 함수를 사용하여 복잡한 decode 함수가 포함된 이해하기 어렵고 직관적이지 않은 코드를 작성할 필요 없이 관계형 테이블에 대한 교차 테이블 보고서를 생성할 수 있습니다. 마찬가지로 unpivot 작업을 사용하여 교차 테이블 보고서를 변환하여 일반 관계형 테이블 형식으로 저장할 수 있습니다. Pivot은 일반 텍스트 또는 XML 형식의 출력을 생성할 수 있습니다. XML 형식의 출력인 경우 pivot 작업이 검색해야 하는 값 도메인을 지정할 필요가 없습니다.