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),120),CUST_NAME
     UNION ALL
     SELECT CONVERT(CHAR(10),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),120)
     ) A
GROUP BY Cust_Name


--218,SUM(
      CASE WHEN CONVERT(CHAR(10),120)='2009-08-01' THEN Qty* Price END
      ) "2009-08-01",120) ='2009-08-02' THEN Qty* Price  END
      ) "2009-08-02",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-03' THEN AR END) "2009-08-03"
FROM 
    (
    SELECT TO_CHAR(Order_Date,'YYYY-MM-DD')Order_Date,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,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,"2009-08-01","2009-08-02","2009-08-03"
FROM 
  (
  SELECT CONVERT(CHAR(10),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),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,"2009-08-03"
FROM 
  (
  SELECT TO_CHAR(Order_Date,Cust_Name
         --,Qty
         --,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,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 SERVER
DROP TABLE Orders_Pivot
G0

SELECT Cust_Name,MAX(CASE WHEN Order_Date ='2009-08-03' THEN AR END) "2009-08-03"
       INTO Orders_Pivot
FROM 
    (
    SELECT CONVERT(CHAR(10),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),CUST_NAME
    ) A
GROUP BY Cust_Name

--226,ORACLE
DROP TABLE Orders_Pivot;

CREATE TABLE Orders_Pivot
AS
SELECT Cust_Name,CUST_NAME
    ) A
GROUP BY Cust_Name 


--227,AR
FROM 
     (?
     SELECT CAST('2009-08-01' AS datetime) Order_Date -- SQL SERVER
            --DATE'2009-08-01' -- ORACLE,"2009-08-01" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT CAST('2009-08-02' AS datetime) Order_Date,"2009-08-02" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT CAST('2009-08-03' AS datetime) Order_Date,"2009-08-03" AR
     FROM Orders_Pivot
     ) A
WHERE AR IS NOT NULL


--227,ORACLE
SELECT Order_Date,AR
FROM 
     (?
     SELECT DATE'2009-08-01' Order_Date,"2009-08-01" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT DATE'2009-08-02' Order_Date,"2009-08-02" AR
     FROM Orders_Pivot
     UNION ALL
     SELECT DATE'2009-08-03' Order_Date,"2009-08-03" AR
     FROM Orders_Pivot
     ) A
WHERE AR IS NOT NULL 

--228,AR,SUBSTRING(Order_Date,6,5) "Date"
FROM 
  (
  SELECT Cust_Name,"2009-08-03"  
  FROM Orders_Pivot
  ) D
UNPIVOT
  (
     AR FOR Order_Date 
          IN ([2009-08-01],"2009-08-03") 
  ) P

--228,SUBSTR(Order_Date,"2009-08-03"  
  FROM Orders_Pivot
  ) D
UNPIVOT
  (
     AR FOR Order_Date 
          IN ("2009-08-01","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,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,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

--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,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY Major) FldIdx
      FROM Specialty
      ) A
GROUP BY EmpName
ORDER BY COUNT(*)DESC

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!