ORACLE 动态的行列转换语句
解决方案 »
- Oracle从一个用户连接到另一个用户的问题
- powerdesigner修改已有数据的oracle结构,总是报错,约束,视图,触发器,包都不好使
- oracle dblink 出现的问题
- oracle中有没有什么内部函数可以返回上一语句处理的行数
- 请帮忙探讨一个存储过程,分不够马上加分!!!!
- 请问如下一条SQL语句该怎么写?
- 急!Oracle8.05件不了表名是中文的表
- ORACLE在P4系统windows2000 Professional 的安装问题(高分求解)
- 8。0。6的dmp文件,导入8。1。7中,导入导致进程停滞?
- oracle多个数据库管理
- ajax中的消息框(如:删除成功!), 为乱码(显示为???),请高手帮我诊断一下是不是数据库的问题?(内有oracle中字符集设置)
- oracle将一张表中的多行合成一行,哪位好心的大哥帮忙,小的在线急用
http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html
回复内容太短了!cao csdn 大爷,整个这个咚咚出来
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;SELECT * FROM t_change_lc;SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'q AS q FROM t_change_lc; SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code,
substr(t.q,
instr(';' t.q, ';', 1, rn),
instr(t.q ';', ';', 1, rn) - instr(';' t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' a.q, ';', 1, rn) > 0) t
ORDER BY 1, 2;
去看我刚发表的文章吧:“ORACLE DB行列转换 ”!
或许对你有用
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1; SELECT * FROM t_change_cl; SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
静态写法1、case when 写法 select 姓名,
max(case 课程 when '数学' then 分数 else 0 end 数学,
max(case 课程 when '语文' then 分数 else 0 end 语文,
max(case 课程 when '物理' then 分数 else 0 end 物理
from TB group by 姓名2、decode写法
select 姓名,
max(decode (课程,'数学',分数,0)) 数学,
max(decode (课程,'语文',分数,0)) 语文,
max(decode (课程,'物理',分数,0)) 物理
from TB group by 姓名动态写法declare
lv_sql varchar2(1000);
sql_command varchar2(5000);
cursor cur is select 课程 from TB group by 课程;
begin
sql_command := 'select 姓名';
for i in cur loop
-- decode 写法
sql_command := sql_command||' , max(decode(课程,'''||i.课程||''', 分数,0)) ' ||i.课程;
-- case when 写法
sql_command := sql_command||',max(case 课程 when '''||i.课程||''' then 分数 else 0 end ' || i.课程;
end loop;
sql_command := sql_command||' from TB group by 姓名'; lv_sql := 'insert into temp_ss ' || sql_command;
dbms_output.put_line(lv_sql);
execute immediate lv_sql;end;