Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.