SqlServer和Oracle中一些常用的sql语句3 行列转换

来源：互联网发布：c windows system32 编辑：程序博客网时间：2024/05/02 00:30

--217, SQL SERVERSELECT 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)     ) AGROUP BY Cust_Name--218, SQL SERVERSELECT 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) +1GROUP BY Cust_Name--218, ORACLESELECT 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    ) AGROUP BY Cust_Name --220, SQL SERVERSELECT 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 DPIVOT  (  SUM(AR)  FOR Order_Date         IN ([2009-08-01], [2009-08-02], "2009-08-03")  ) AS P--220, SQL SERVERSELECT 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 DPIVOT  (    SUM(AR)           FOR Cust_Name                IN ([李先生], [张先生], [曹先生], [陈先生])  ) AS P  --222, ORACLESELECT 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') DPIVOT  (  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, ORACLESELECT *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

--226, SQL SERVERDROP TABLE Orders_PivotG0SELECT 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_PivotFROM     (    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    ) AGROUP BY Cust_Name--226, ORACLEDROP TABLE Orders_Pivot;CREATE TABLE Orders_PivotASSELECT 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    ) AGROUP BY Cust_Name --227, SQL SERVERSELECT Order_Date, Cust_Name, ARFROM      (     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     ) AWHERE AR IS NOT NULL--227, ORACLESELECT Order_Date, Cust_Name, ARFROM      (     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     ) AWHERE AR IS NOT NULL --228, SQL SERVERSELECT 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  ) DUNPIVOT  (     AR FOR Order_Date           IN ([2009-08-01], [2009-08-02], "2009-08-03")   ) P--228, ORACLESELECT 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  ) DUNPIVOT  (     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.NFROM Orders_Pivot P, Tally IWHERE 1=1       AND N<=3ORDER BY 1, 4 --231, ORALCE / SQL SERVERSELECT Order_Date       , Cust_Name       , ARFROM     (    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    ) AWHERE AR IS NOT NULL

--234, ORACLE/SQL SERVER SELECT EmpName      , Major      , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdxFROM Specialty--236, ORACLE / SQL SERVERSELECT 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(*) FldCountFROM        (      SELECT EmpName              , Major              , ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx      FROM Specialty      ) AGROUP BY EmpNameORDER BY COUNT(*)DESC