Oracle 行列转置两种简单的行列转置:1、固定列数的行列转换如studentsubjectgrade--------- ---------- ---
Oracle 行列转置
两种简单的行列转置:
1、固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
语句如下：
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
FROM twb06) )
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
FROM twb06) )
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
FROM twb06) )
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
FROM twb06)
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
FROM twb06)
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
FROM twb06)
WHERE ROWNUM=1
http://blog.csdn.net/hynet/archive/2007/03/22/1537380.aspx
