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