Oracle 列转行&行转列
列转行
方法一：
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
select * from tb_student;
select
name,
sum(case course when '数学' then score else null end) 数学,
sum(case course when '英语' then score else null end) 英语,
sum(case course when '语文' then score else null end) 语文
from
tb_student
group by name;
create table zyy1(
ID1 number,
ID2 number,
VALUE1 VARCHAR2(20),
VALUE2 VARCHAR2(20)
)
INSERT INTO ZYY1 VALUES(1,2,'A','B');
INSERT INTO ZYY1 VALUES(1,2,'C','D');
INSERT INTO ZYY1 VALUES(1,2,'E','F');
INSERT INTO ZYY1 VALUES(1,2,'G','H');
INSERT INTO ZYY1 VALUES(3,8,'I','J');
INSERT INTO ZYY1 VALUES(3,8,'K','L');
INSERT INTO ZYY1 VALUES(3,8,'M','N');
INSERT INTO ZYY1 VALUES(8,9,'O','P');
INSERT INTO ZYY1 VALUES(8,9,'Q','R');
INSERT INTO ZYY1 VALUES(11,12,'S','T');
commit;
select ID1,ID2,
MAX(DECODE(RN,1,VALUE1)),
MAX(DECODE(RN,1,VALUE2)),
MAX(DECODE(RN,2,VALUE1)),
MAX(DECODE(RN,2,VALUE2)),
MAX(DECODE(RN,3,VALUE1)),
MAX(DECODE(RN,3,VALUE2))
FROM (SELECT ZYY1.*, ROW_NUMBER() OVER(partition by ID1,ID2 order by value1,value2) rn from zyy1) T
where rn<=3
group by ID1,ID2;
--北京耗材中使用的例子
select hosid,
sum(case CATALOGNAME
when '国械注准20173461407' then
cnts
else
null
end) 国械注准20173461407,
sum(case CATALOGNAME
when '国械注进20173461507' then
cnts
else
null
end) 国械注进20173461507,
sum(case CATALOGNAME
when '国械注进20173466565' then
cnts
else
null
end) 国械注进20173466565
from (select hosid,
BASE_CATALOG.CATALOGNAME CATALOGNAME,
sum(BASE_DEMANDSCALE.Purcount) cnts
from BASE_CATALOG
left join BASE_DEMANDSCALE
on BASE_DEMANDSCALE.CATALOGUEID = BASE_CATALOG.catalogid
where hosid = 'BJ_H0001'
group by hosid, catalogid, CATALOGNAME)
group by hosid;
方法二 pivot：
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',86);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
select name, 语文, 数学, 英语
from (select name, course, score from tb_student)
pivot(max(score) for course in('语文' 语文,'数学' 数学,'英语' 英语))
order by name
select hosid,
国械注准20173461407,
国械注进20173461507,
国械注进20173466565
from (select hosid, CATALOGNAME, sum(BASE_DEMANDSCALE.Purcount) cnts
from BASE_CATALOG
left join BASE_DEMANDSCALE
on BASE_DEMANDSCALE.CATALOGUEID = BASE_CATALOG.catalogid
group by hosid, catalogid, CATALOGNAME
order by hosid, CATALOGNAME) pivot(max(cnts) for CATALOGNAME in('国械注准20173461407'
国械注准20173461407,
'国械注进20173461507'
国械注进20173461507,
'国械注进20173466565'
国械注进20173466565
))
order by hosid;
行转列：
方法一 UNPIVOT：
CREATE TABLE pvt (VendorID int , Emp1 int , Emp2 int ,
Emp3 int , Emp4 int , Emp5 int );
INSERT INTO pvt VALUES ( 1 , 4 , 3 , 5 , 4 , 4 );
INSERT INTO pvt VALUES ( 2 , 4 , 1 , 5 , 5 , 5 );
INSERT INTO pvt VALUES ( 3 , 4 , 3 , 5 , 4 , 4 );
INSERT INTO pvt VALUES ( 4 , 4 , 2 , 5 , 5 , 4 );
INSERT INTO pvt VALUES ( 5 , 5 , 1 , 5 , 5 , 5 );
SELECT VendorID, Employee, Orders
FROM
( SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
) ;
