Oracle uses SQL to implement matrix transpose

When you use a report with a database, you often encounter matrix transpose. This requirement in Excel is easy to implement, but many people do not know how to use the Oracle database implementation, the following shows you a few ways to use SQL implementation.

Requirements: Table 1 transpose into table 2

Test data:

CREATE TABLETmp as SELECT 'A1'  asA'B1'  asB fromDUALUNION  AllSELECT 'A2'  asA'B2'  asB fromDUALUNION  AllSELECT 'A3'  asA'B3'  asB fromDUALUNION  AllSELECT 'A4'  asA'B4'  asB fromDUALUNION  AllSELECT 'A5'  asA'B5'  asB fromDUAL

Method One: Unpivot+pivot database above 11g

SELECT * from(SELECTR, COL, V from(SELECTRow_number () Over(ORDER  byA, B) asR, A, B fromTMP) UNPIVOT (V forCOLinch(A, B))) PIVOT (MAXV forRinch(1,2,3,4,5));

Method Two: Max+decode

SELECTCOL,MAX(DECODE (R,1, V)),MAX(DECODE (R,2, V)),MAX(DECODE (R,3, V)),MAX(DECODE (R,4, V)),MAX(DECODE (R,5, V)) from(SELECTT.R, DECODE (T1. L1,'A',2,'B') asCOL, DECODE (T1. L,1A2, B) V from(SELECTRow_number () Over(ORDER  byA, B) asR, A, B fromTMP) T, (SELECT  LevelL fromDUAL CONNECT by  Level <= 2) T1)GROUP   byCOL

METHOD Three: Model clause database above 10g

SELECTDECODE (R,1,'A','B') asCOL, V1, V2, V3, V4, V5 fromTmpmodelRETURNUPDATED rowsdimension by(Row_number () Over(ORDER  byb) asR) MEASURES (A,b,a asV1,a asV2,a asV3,a asV4,a asV5) RULES (V1[r<=2]=DECODE (CV (R),1A[1],2B[1]), V2[r<=2]=DECODE (CV (R),1A[2],2B[2]), V3[r<=2]=DECODE (CV (R),1A[3],2B[3]), V4[r<=2]=DECODE (CV (R),1A[4],2B[4]), V5[r<=2]=DECODE (CV (R),1A[5],2B[5]))

Oracle uses SQL to implement matrix transpose