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