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


语句如下：

select student, 
       sum(decode(subject,'语文', grade,null)) "语文",
       sum(decode(subject,'数学', grade,null)) "数学",
       sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;

2、不定列行列转换
如
c1  c2
--- -----------
1   我
1   是
1   谁
2   知
2   道
3   不
……
转换为
1 我是谁
2 知道
3 不

这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
    RETURN VARCHAR2
IS
    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