Oracle中行列转换方法...

实际查询中，可能会出现将某些查询结果值显示在列位置上，此时可之直接通过sql语句进行处理：

1. 方法-：使用SQL的函数转换：

 SELECT aa.po_no,aa.po_line,
nvl((case when aa.Group_Name='RE' THEN aa.Qty END),0) AS Re,
nvl((case when aa.Group_Name='SH' THEN aa.Qty END),0) AS Sh,
nvl((case when aa.Group_Name='PA' THEN aa.Qty END),0) AS Pa,
nvl((case when aa.Group_Name='WE' THEN aa.Qty END),0)AS We
 FROM  (SELECT s.PO, s.LINE, S.GROUP_NAME, COUNT(*) AS QTY
  FROM table1 s, table2 v1
 WHERE   V.PO = S.PO AND V.PO = S.line AND S.GROUP_NAME IN ('RE', 'SH', 'PA', 'WE')
   AND V1.DATE BETWEEN TO_DATE('20100605', 'yyyymmdd') AND
       TO_DATE('20100607', 'yyyymmdd'))aa

 

2. 方法=：直接通过串表得到：

SELECT V.PO,V.LINE,

      (SELECT COUNT(*)
          FROM table1 S
         WHERE V.PO = S.PO AND V.PO = S.line
           AND S.GROUP_NAME ='SH') AS SH,
       (SELECT COUNT(*)
          FROM table1 S
         WHERE V.PO = S.PO AND V.PO = S.line
           AND S.GROUP_NAME ='RE') AS RE,
        (SELECT COUNT(*)
          FROM table1 S
         WHERE V.PO = S.PO AND V.PO = S.line

           AND S.GROUP_NAME ='PA') AS PA, 
        (SELECT COUNT(*)
          FROM table1 S
         WHERE V.PO = S.PO AND V.PO = S.line
           AND S.GROUP_NAME ='WE') AS WE,                   
  FROM table2 V
  WHERE V.DATE BETWEEN TO_DATE('20100605', 'yyyymmdd') AND TO_DATE('20100607', 'yyyymmdd')