Column-column conversion and how to use pivot clause in Oracle
This article will explain in detail how to use row-column conversion and pivot clause in Oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.
I. the method of column-column conversion before 11g
Create a test table:
Create table emp_phone (name varchar2 (50), type char, phone varchar2 (50))
Insert into emp_phone values ('Zhang San', '1Qing,' 1234-5678')
Insert into emp_phone values ('Zhang San', '2Qing,' 3219-6066')
Insert into emp_phone values ('Zhang San', '313', '5365-9583')
Insert into emp_phone values ('Li Si', '1Qing,' 6837-2745')
Insert into emp_phone values ('Li Si', '313', '2649-5820')
Insert into emp_phone values ('Wang Wu', '1Qing,' 5838-9002')
Insert into emp_phone values ('Wang Wu', '2Qing,' 2749-5580')
Insert into emp_phone values ('Chen Liu', '2Qing,' 9876-3453')
Commit
Select * from emp_phone
NAME T PHONE
Zhang San 1 1234-5678
Zhang San 2 3219-6066
Zhang San 3 5365-9583
Li Si 1 6837-2745
Li Si 3 2649-5820
Wang Wu 1 5838-9002
Wang Wu 2 2749-5580
Chen Liu2 9876-3453
Column wrapping:
Select name, max (decode (type,'1, phone)) home, max (decode (type,'2, phone)) office, max (decode (type,'3, phone)) mobile from emp_phone group by name
NAME HOME OFFICE MOBILE
--
Chen Liu 9876-3453
Wang Wu 5838-9002 2749-5580
Li Si 6837-2745 2649-5820
Zhang San 1234-5678 3219-6066 5365-9583
Row turn back to column:
Create table emp_phone1 as
(select name, max (decode (type,'1, phone)) home, max (decode (type,'2, phone)) office, max (decode (type,'3, phone)) mobile from emp_phone group by name)
Select * from emp_phone1
NAME HOME OFFICE MOBILE
--
Chen Liu 9876-3453
Wang Wu 5838-9002 2749-5580
Li Si 6837-2745 2649-5820
Zhang San 1234-5678 3219-6066 5365-9583
Select name, decode (lvl, 1, home, 2, office, 3, mobile) phone
From emp_phone1, (select level lvl from dual connect by level
