Oracle 行列转置
两种简单的行列转置:1、固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1语文 80
student1数学 70
student1英语 60
student2语文 90
student2数学 80
student2英语 100
……
转换为
语文 数学 英语
student180 70 60
student290 80 100
语句如下：
select student, sum(decode(subject,'语文', grade,null)) "语文", sum(decode(subject,'数学', grade,null)) "数学", sum(decode(subject,'英语', grade,null)) "英语"from tablegroup by student;
2、不定列行列转换
如
c1c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) RETURN VARCHAR2IS Col_c2 VARCHAR2(4000);BEGIN FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP Col_c2 := Col_c2||cur.c2; END LOOP; Col_c2 := rtrim(Col_c2,1); RETURN Col_c2;END;/SQL> select distinct c1 ,get_c2(c1) cc2 from table;
本文来自CSDN博客，转载请标明出处：
如何用SQL实现一个结果集的行列转置？
以此为准：
with tt as (select 1 a , 2 b , 3 c from dual
union all
select 2 a , 2 b , 4 c from dual
union all
select 4 a , 3 b , 3 c from dual
union all
select 3 a , 1 b , 6 c from dual
union all
select 4 a , 3 b , 1 c from dual
union all
select 6 a , 5 b , 9 c from dual)
谢谢！！
能说明白一点吗？
select 1 a , 2 b , 3 c from dual
union all
select 2 a , 2 b , 4 c from dual
union all
select 4 a , 3 b , 3 c from dual
union all
select 3 a , 1 b , 6 c from dual
union all
select 4 a , 3 b , 1 c from dual
union all
select 6 a , 5 b , 9 c from dual
结果为：
A B C
1 2 3
2 2 4
4 3 3
3 1 6
4 3 1
6 5 9
给这个变成什么样？
可以这样！！！！！1
先建立个表
create table TWB06
(
A VARCHAR2(10),
B VARCHAR2(10),
C VARCHAR2(20)
)
插入数据
A B C
1 2 3
2 2 4
4 3 3
3 1 6
4 3 1
6 5 9
然后这样，就行了
SELECT * FROM (SELECT * FROM (SELECT A A1,LEAD(A,1) OVER (ORDER BY ROWNUM) A2,
LEAD(A,2) OVER (ORDER BY ROWNUM) A3,
LEAD(A,3) OVER (ORDER BY ROWNUM) A4,
LEAD(A,4) OVER (ORDER BY ROWNUM) A5,
LEAD(A,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06) )
WHERE ROWNUM=1 UNION
SELECT * FROM (SELECT * FROM (SELECT B A1,LEAD(B,1) OVER (ORDER BY ROWNUM) A2,
LEAD(B,2) OVER (ORDER BY ROWNUM) A3,
LEAD(B,3) OVER (ORDER BY ROWNUM) A4,
LEAD(B,4) OVER (ORDER BY ROWNUM) A5,
LEAD(B,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06) )
WHERE ROWNUM=1
UNION
SELECT * FROM (SELECT * FROM (SELECT C A1,LEAD(C,1) OVER (ORDER BY ROWNUM) A2,
LEAD(C,2) OVER (ORDER BY ROWNUM) A3,
LEAD(C,3) OVER (ORDER BY ROWNUM) A4,
LEAD(C,4) OVER (ORDER BY ROWNUM) A5,
LEAD(C,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06) )
WHERE ROWNUM=1
刚才写多了，这样就行
SELECT * FROM (SELECT A A1,LEAD(A,1) OVER (ORDER BY ROWNUM) A2,
LEAD(A,2) OVER (ORDER BY ROWNUM) A3,
LEAD(A,3) OVER (ORDER BY ROWNUM) A4,
LEAD(A,4) OVER (ORDER BY ROWNUM) A5,
LEAD(A,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06)
WHERE ROWNUM=1 union SELECT * FROM (SELECT B A1,LEAD(B,1) OVER (ORDER BY ROWNUM) A2,
LEAD(B,2) OVER (ORDER BY ROWNUM) A3,
LEAD(B,3) OVER (ORDER BY ROWNUM) A4,
LEAD(B,4) OVER (ORDER BY ROWNUM) A5,
LEAD(B,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06)
WHERE ROWNUM=1
union
SELECT * FROM (SELECT C A1,LEAD(C,1) OVER (ORDER BY ROWNUM) A2,
LEAD(C,2) OVER (ORDER BY ROWNUM) A3,
LEAD(C,3) OVER (ORDER BY ROWNUM) A4,
LEAD(C,4) OVER (ORDER BY ROWNUM) A5,
LEAD(C,5) OVER (ORDER BY ROWNUM) A6
FROMtwb06)
WHERE ROWNUM=1
http://blog.csdn.net/hynet/archive/2007/03/22/1537380.aspx
页:
[1]