《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換

taogchan發表於2013-07-03
SQLServerOracle
SqlServer和Oracle中一些常用的sql語句3-行列轉換
 
《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換
 
[sql] 
--217, SQL SERVER  
SELECT Cust_Name  
       , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"  
       , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"  
       , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"  
FROM   
    (  
     SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date  
           , CUST_NAME  
           , SUM(Qty * Price) AR  
     FROM Orders  
     WHERE Order_Date BETWEEN '2009-08-01'   
                                 AND CAST('2009-08-03' AS datetime) +1  
     GROUP BY CONVERT(CHAR(10), Order_Date, 120)  
               , CUST_NAME  
     UNION ALL  
     SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date  
            ,NULL CUST_NAME  
            , SUM(Qty * Price) AR  
     FROM Orders  
     WHERE Order_Date BETWEEN '2009-08-01'   
                         AND CAST('2009-08-03' AS datetime) +1  
     GROUP BY CONVERT(CHAR(10), Order_Date, 120)  
     ) A  
GROUP BY Cust_Name  
  
  
--218, SQL SERVER  
SELECT Cust_Name  
, SUM(  
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120)='2009-08-01' THEN Qty* Price END  
      ) "2009-08-01"  
, SUM(  
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120) ='2009-08-02' THEN Qty* Price  END  
      ) "2009-08-02"  
, SUM(  
      CASE WHEN CONVERT(CHAR(10),Order_Date, 120) ='2009-08-03' THEN Qty* Price  END  
      ) "2009-08-03"  
FROM Orders   
WHERE 1=1  
      AND Order_Date BETWEEN '2009-08-01' AND CAST('2009-08-03' AS datetime) +1  
GROUP BY Cust_Name  
  
--218, ORACLE  
SELECT Cust_Name  
      , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"  
      , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"  
      , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"  
FROM   
    (  
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD')Order_Date  
           , CUST_NAME  
         , SUM(Qty * Price) AR  
    FROM Orders  
    WHERE Order_Date BETWEEN DATE'2009-08-01'   
                       AND DATE'2009-08-03' +1  
    GROUP BY TO_CHAR(Order_Date, 'YYYY-MM-DD')  
              , CUST_NAME  
    UNION ALL  
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date  
           ,NULL CUST_NAME  
           , SUM(Qty * Price) AR  
    FROM Orders  
    WHERE Order_Date BETWEEN DATE'2009-08-01'   
                       AND TO_DATE('2009-08-03', 'YYYY-MM-DD') +1  
    GROUP BY Order_Date  
    ) A  
GROUP BY Cust_Name   
  
--220, SQL SERVER  
SELECT Cust_Name  
       , "2009-08-01"  
       , "2009-08-02"  
       , "2009-08-03"  
FROM   
  (  
  SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date  
        , Cust_Name  
        , Qty * Price AR  
  FROM Orders  
  WHERE 1=1  
        AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'  
  )AS D  
PIVOT  
  (  
  SUM(AR)  
  FOR Order_Date   
        IN ([2009-08-01], [2009-08-02], "2009-08-03")  
  ) AS P  
--220, SQL SERVER  
SELECT Order_Date  
       ,[李先生]   
       , [張先生]  
       , [曹先生]  
       , [陳先生]  
FROM   
  (  
  SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date  
           , Cust_Name  
           , Qty  
           , Price  
           , Qty*Price AS AR  
  FROM Orders  
  WHERE 1=1  
        AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'  
  )AS D  
PIVOT  
  (  
    SUM(AR)  
           FOR Cust_Name   
               IN ([李先生], [張先生], [曹先生], [陳先生])  
  ) AS P  
    
--222, ORACLE  
SELECT Cust_Name  
       , "2009-08-01"  
       , "2009-08-02"  
       , "2009-08-03"  
FROM   
  (  
  SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') Order_Date  
         , Cust_Name  
         --, Qty  
         --, Price  
         , Qty * Price AR  
  FROM ORDERS  
  WHERE 1=1  
         AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'  
) D  
PIVOT  
  (  
  SUM(AR)  
  FOR Order_Date  
       IN ('2009-08-01' AS "2009-08-01", '200908-02' "2009-08-02", '2009-08-03' "2009-08-03")  
  ) P  
  
--223, ORACLE  
SELECT *  
FROM   
    (  
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD') OrderDate  
          , Cust_Name  
          , Qty  
          , Price  
    FROM Orders  
    WHERE 1=1  
           AND Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'  
    )  
    PIVOT  
    (     
       SUM(Qty * Price) AS "AR"  
       , COUNT(*) AS "Qty"   
       FOR OrderDate  
             IN ('2009-08-01' AS "08-01", '2009-08-02' "08-02", '2009-08-03' "08-03")  
    ) P  
      
 
《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換
 
[sql] 
--226, SQL SERVER  
DROP TABLE Orders_Pivot  
G0  
  
SELECT Cust_Name  
       , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"  
       , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"  
       , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"  
       INTO Orders_Pivot  
FROM   
    (  
    SELECT CONVERT(CHAR(10), Order_Date, 120) Order_Date  
           , CUST_NAME  
           , SUM(Qty * Price) AR  
    FROM Orders  
    WHERE 1=1  
           AND Order_Date BETWEEN '2009-08-01' AND CAST('2009-08-03' AS datetime) +1  
    GROUP BY CONVERT(CHAR(10), Order_Date, 120)  
             , CUST_NAME  
    ) A  
GROUP BY Cust_Name  
  
--226, ORACLE  
DROP TABLE Orders_Pivot;  
  
CREATE TABLE Orders_Pivot  
AS  
SELECT Cust_Name  
      , MAX(CASE WHEN Order_Date ='2009-08-01' THEN AR END) "2009-08-01"  
      , MAX(CASE WHEN Order_Date ='2009-08-02' THEN AR END) "2009-08-02"  
      , MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"  
FROM   
    (  
    SELECT TO_CHAR(Order_Date, 'YYYY-MM-DD')Order_Date  
           , CUST_NAME  
         , SUM(Qty * Price) AR  
    FROM Orders  
    WHERE Order_Date BETWEEN DATE'2009-08-01'   
                       AND DATE'2009-08-03' +1  
    GROUP BY TO_CHAR(Order_Date, 'YYYY-MM-DD')  
              , CUST_NAME  
    ) A  
GROUP BY Cust_Name   
  
  
--227, SQL SERVER  
SELECT Order_Date, Cust_Name, AR  
FROM   
     (  
     SELECT CAST('2009-08-01' AS datetime) Order_Date -- SQL SERVER  
            --DATE'2009-08-01' -- ORACLE  
            , Cust_Name  
            , "2009-08-01" AR  
     FROM Orders_Pivot  
     UNION ALL  
     SELECT CAST('2009-08-02' AS datetime) Order_Date  
            , Cust_Name  
            , "2009-08-02" AR  
     FROM Orders_Pivot  
     UNION ALL  
     SELECT CAST('2009-08-03' AS datetime) Order_Date  
            , Cust_Name  
            , "2009-08-03" AR  
     FROM Orders_Pivot  
     ) A  
WHERE AR IS NOT NULL  
  
  
--227, ORACLE  
SELECT Order_Date, Cust_Name, AR  
FROM   
     (?  
     SELECT DATE'2009-08-01' Order_Date  
            , Cust_Name  
            , "2009-08-01" AR  
     FROM Orders_Pivot  
     UNION ALL  
     SELECT DATE'2009-08-02' Order_Date  
            , Cust_Name  
            , "2009-08-02" AR  
     FROM Orders_Pivot  
     UNION ALL  
     SELECT DATE'2009-08-03' Order_Date  
            , Cust_Name  
            , "2009-08-03" AR  
     FROM Orders_Pivot  
     ) A  
WHERE AR IS NOT NULL   
  
--228, SQL SERVER  
SELECT Order_Date   
       , Cust_Name  
       , AR  
       , SUBSTRING(Order_Date, 6,5) "Date"  
FROM   
  (  
  SELECT Cust_Name, "2009-08-01" , "2009-08-02", "2009-08-03"    
  FROM Orders_Pivot  
  ) D  
UNPIVOT  
  (  
     AR FOR Order_Date   
          IN ([2009-08-01], [2009-08-02], "2009-08-03")   
  ) P  
  
--228, ORACLE  
SELECT Order_Date   
       , Cust_Name  
       , AR  
       , SUBSTR(Order_Date, 6,5) "Date"  
FROM   
  (  
  SELECT Cust_Name, "2009-08-01" , "2009-08-02", "2009-08-03"    
  FROM Orders_Pivot  
  ) D  
UNPIVOT  
  (  
     AR FOR Order_Date   
          IN ("2009-08-01", "2009-08-02", "2009-08-03")   
  ) P  
    
--230, ORALCE / SQL SERVER   
SELECT P.Cust_Name  
       , P."2009-08-01"  
       , P."2009-08-02"  
       , P."2009-08-03"  
       , I.N  
FROM Orders_Pivot P, Tally I  
WHERE 1=1  
       AND N<=3  
ORDER BY 1, 4  
   
  
--231, ORALCE / SQL SERVER  
SELECT Order_Date  
       , Cust_Name  
       , AR  
FROM   
    (  
    SELECT   
         CASE N WHEN 1 THEN '2009-08-01'  
                 WHEN 2 THEN '2009-08-02'  
                 WHEN 3 THEN '2009-08-03'  
         END Order_Date  
         , Cust_Name  
         , CASE N WHEN 1 THEN "2009-08-01"  
                   WHEN 2 THEN "2009-08-02"  
                   WHEN 3 THEN "2009-08-03"  
          END AR  
    FROM Orders_Pivot P, Tally I   
    WHERE 1=1  
          AND N<=3  
    ) A  
WHERE AR IS NOT NULL  
 
 
《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換
 
[sql] 
--234, ORACLE/SQL SERVER   
SELECT EmpName  
      , Major  
      , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx  
FROM Specialty  
  
--236, ORACLE / SQL SERVER  
SELECT EmpName  
      , MAX(CASE WHEN FldIdx=1 THEN Major END) Fld1  
      , MAX(CASE WHEN FldIdx=2 THEN Major END) Fld2  
      , MAX(CASE WHEN FldIdx=3 THEN Major END) Fld3  
      , COUNT(*) FldCount  
FROM   
       (  
      SELECT EmpName  
              , Major  
              , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx  
      FROM Specialty  
      ) A  
GROUP BY EmpName  
ORDER BY COUNT(*)DESC  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22392018/viewspace-765389/，如需轉載，請註明出處，否則將追究法律責任。

相關文章