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

2022-07-11 17:02:12

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

--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 行列转换

--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 行列转换

--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

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

上一篇：java中 i = i++ 的结果


下一篇：Cubieboard2裸机开发之（二）板载LED交替闪烁