SqlServer和Oracle中一些常用的sql语句3-行列转换
SqlServer和Oracle中一些常用的sql语句2
http://www.2cto.com/database/201306/218530.
html
[sql]
--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
