17,086
社区成员
发帖
与我相关
我的任务
分享求两种形式的行转列
原始表1
SID A B C
2001 80 81 87
2002 60 62 63
2003 72 77 79
原始表2
LID TYPE CHN
1 A 语文
4 B 数学
5 C 英语
希望出现效果1:
SID 语文 数学 英语
2001 80 81 87
2002 60 62 63
2003 72 77 79
效果2:
TMP 2001 2002 2003
语文 80 60 72
数学 81 62 77
英语 87 63 79
SID A B C
2001 80 81 87
2002 60 62 63
2003 72 77 79
原始表2
LID TYPE CHN
1 A 语文
4 B 数学
5 C 英语
希望出现效果1:
SID 语文 数学 英语
2001 80 81 87
2002 60 62 63
2003 72 77 79
效果2:
TMP 2001 2002 2003
语文 80 60 72
数学 81 62 77
英语 87 63 79
...全文
请发表友善的回复…
发表回复
碧水幽幽泉 2017-11-25
- 打赏
- 举报
--创建表并插入模拟数据
create table tt1(sid number,a number,b number,c number);
create table tt2(lid number,type varchar2(10),chn varchar2(10));
insert into tt1
select 2001,80,81,87 from dual union all
select 2002,60,62,63 from dual union all
select 2003,72,77,79 from dual;
insert into tt2
select 1,'A','语文' from dual union all
select 4,'B','数学' from dual union all
select 5,'C','英语' from dual;
--效果1
create or replace procedure tt1_converts authid current_user
as
v_colname tt2.chn%type;
sqlstr varchar2(500);
begin
for cur in (select column_name
from user_tab_cols
where table_name = 'TT1'
and column_id > 1
order by column_id
) loop
select chn
into v_colname --语文、数学、英语
from tt2
where type = cur.column_name;
sqlstr := sqlstr || ',' || cur.column_name; --A、B、C
if v_colname is not null then
sqlstr := sqlstr || ' "' || v_colname || '"'; --A "语文"、B "数学"、C "英语"
end if;
end loop;
execute immediate 'create or replace view v_tt1 as select ' || substr(sqlstr, 2) || ' from tt1';
end;
/
--效果2
--1.sql
select b.chn tmp, a."2001", a."2002", a."2003"
from (select 'A' tmp,
max(decode(sid, 2001, "A")) "2001",
max(decode(sid, 2002, "A")) "2002",
max(decode(sid, 2003, "A")) "2003"
from tt1
union all
select 'B' tmp,
max(decode(sid, 2001, "B")) "2001",
max(decode(sid, 2002, "B")) "2002",
max(decode(sid, 2003, "B")) "2003"
from tt1
union all
select 'C' tmp,
max(decode(sid, 2001, "C")) "2001",
max(decode(sid, 2002, "C")) "2002",
max(decode(sid, 2003, "C")) "2003"
from tt1) a,
tt2 b
where a.tmp = b.type;
--2.使用存储过程动态拼接
create or replace procedure tt2_converts authid current_user
is
sqlstr varchar2(2000);
sqlstr1 varchar2(500);
v_tmp varchar2(200);
begin
for cur in (select column_name
from user_tab_cols
where table_name = 'TT1'
and column_id > 1
) loop
sqlstr := sqlstr || chr(10) || case when sqlstr is not null then 'union all ' end || 'select ''' || cur.column_name || ''' tmp';
sqlstr1 := null;
for cur1 in (select sid, rownum rn from tt1) loop
sqlstr1 := sqlstr1 || ',max(decode(sid,' || cur1.sid || ',"' || cur.column_name || '")) "' || cur1.sid || '"';
if nvl(length(v_tmp) - length(replace(v_tmp, ',')), 0) < cur1.rn then
v_tmp := v_tmp || ',a."' || cur1.sid || '"'; --1(0 < 1) => a."2001"
--2(1 < 2) => a."2001",a."2002"
--3(2 < 3) => a."2001",a."2002",a."2003"
end if;
end loop;
sqlstr := sqlstr || sqlstr1 || ' from tt1';
end loop;
sqlstr := 'create or replace view v_tt1_1 as select b.chn tmp' || v_tmp || ' from (' || sqlstr || ')a,tt2 b where a.tmp=b.type';
execute immediate sqlstr;
end;
/
