oracle 一列全部转为行,2018-12-19 oracle 列转行-行转列

1、列转行：

数据准备：

create table STUDENTSCORES

(

username VARCHAR2(20),

subject  VARCHAR2(30),

score    NUMBER(10,2)

)

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('张三', '语文', 80.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('张三', '数学', 90.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('张三', '英语', 70.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('张三', '生物', 85.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('李四', '语文', 80.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('李四', '数学', 92.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('李四', '英语', 76.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('李四', '生物', 88.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('码农', '语文', 60.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('码农', '数学', 82.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('码农', '英语', 96.00);

insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)

values ('码农', '生物', 78.00);

列转行实现：

SELECT * FROM StudentScores /*数据源*/

PIVOT

( sum(Score)/*行转列后 列的值*/

for Subject /*需要行转列的列*/  in ('语文','数学','英语','生物' 生物,'物理' /*列的名称*/)

)

加条件：

select * from (

SELECT * FROM StudentScores t where t.score>60  /*数据源*/

)

PIVOT

( sum(Score)/*行转列后 列的值*/

for Subject /*需要行转列的列*/  in ('语文','数学','英语','生物' 生物,'物理' /*列的名称*/)

)

2、行转列：

CREATE TABLE ProgrectDetail

(

ProgrectName        VARCHAR(20), --工程名称

OverseaSupply        number(10),          --海外供应商供给数量

NativeSupply        number(10),          --国内供应商供给数量

SouthSupply          number(10),          --南方供应商供给数量

NorthSupply          number(10)          --北方供应商供给数量

)

INSERT INTO ProgrectDetail

SELECT 'A', 100, 200, 50, 50 from dual

UNION ALL

SELECT 'B', 200, 300, 150, 150 from dual

UNION ALL

SELECT 'C', 159, 400, 20, 320 from dual

实现：

SELECT *

FROM ProgrectDetail

UNPIVOT

(

SupplyNum FOR Supplier IN  --

(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )

) P