Oracle使用SQL实现矩阵转置

在使用数据库使用报表时，往往会遇到矩阵转置。这个需求在Excel中，很容易实现的，但很多人都不知道怎么用Oracle数据库实现。

下面给大家展示几种使用SQL实现的方法。

 需求1：表1转置成表2

 

--1.添加测试数据
create table tmp as 
select 'A1' as a, 'B1' as b from dual union all
select 'A2' as a, 'B2' as b from dual union all
select 'A3' as a, 'B3' as b from dual union all
select 'A4' as a, 'B4' as b from dual union all
select 'A5' as a, 'B5' as b from dual;

--2.SQL实现
--方法1：unpivot+pivot (适用于oracle11g或以上)
select *
  from (select r, col, v
          from (select row_number() over(order by a, b) as r, a, b
                  from tmp
               ) unpivot(v for col in(a, b))
       )
  pivot(max(v) for r in(1, 2, 3, 4, 5));
  
--方法2: max+decode
select col, 
       max(decode(rn, 1, v)), 
       max(decode(rn, 2, v)), 
       max(decode(rn, 3, v)),
       max(decode(rn, 4, v)), 
       max(decode(rn, 5, v))
 from (select t.rn, 
              decode(t1.lv, 1, 'A', 2, 'B') as col,
              decode(t1.lv, 1, a, 2, b) v
         from (select row_number() over(order by a, b) as rn, a, b
                 from tmp
              ) t,
             (select level lv from dual connect by level <= 2) t1
       )
group by col;

--方法3: model子句 (适用于oracle10g或以上)
select decode(rn, 1, 'a', 'b') as col, v1, v2, v3, v4, v5
  from tmp
 model
return updated rows
dimension by(row_number()over(order by a,b) as rn)
MEASURES (A,B,A AS V1,A AS V2,A AS V3,A AS V4,A AS V5)
RULES
(
V1[R<=2]=DECODE(CV(R),1,A[1],2,B[1]), 
V2[R<=2]=DECODE(CV(R),1,A[2],2,B[2]), 
V3[R<=2]=DECODE(CV(R),1,A[3],2,B[3]), 
V4[R<=2]=DECODE(CV(R),1,A[4],2,B[4]), 
V5[R<=2]=DECODE(CV(R),1,A[5],2,B[5])
);

  需求2：表2转置成表1

  

--1.添加测试数据
create table tmp1 as 
select 'A' AS COL,'A1' AS V1,'A2' AS V2,'A3' AS V3,'A4' AS V4,'A5' AS V5 from dual
union all
select 'B','B1','B2','B3','B4','B5' from dual;

--2.SQL实现
--方法1: unpivot+pivot (适用于oracle11g或以上)  上面的方法反过来用
select a, b
  from (select *
          from tmp1 
        unpivot(v for col1 in(v1, v2, v3, v4, v5))
        )
  pivot(max(v) for col in('A' as a, 'B' as b))
order by a, b;

--方法2：max+decode
select max(decode(col, 'A', v)) as a, max(decode(col, 'B', v)) as b
 from (select col, lv, decode(lv, 1, v1, 2, v2, 3, v3, 4, v4, 5, v5) as v
         from tmp1,
              (select level as lv from dual connect by level <= 5)
      )
 order by a, b;

--方法3：model子句 (适用于oracle10g或以上)
select a, b
  from tmp1
 model
return updated rows
dimension by(row_number()over(order by col) as r)
measures (v1,v2,v3,v4,v5,v1 as a,v2 as b)
rules iterate(5)
(
A[ITERATION_NUMBER+1]=DECODE(CV(R),1,V1[1],2,V2[1],3,V3[1],4,V4[1],V5[1]),
B[ITERATION_NUMBER+1]=DECODE(CV(R),1,V1[2],2,V2[2],3,V3[2],4,V4[2],V5[2])
);