《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換
SqlServer和Oracle中一些常用的sql語句3-行列轉換
[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 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
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
[sql]
--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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22392018/viewspace-765389/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11新特性——行列轉換語句（二）Oracle
- Oracle11新特性——行列轉換語句（一）Oracle
- oracle和mysql的行列轉換OracleMySql
- 行列轉換sqlSQL
- sqlserver dba常用的sql語句SQLServer
- 轉貼：Oracle維護常用SQL語句OracleSQL
- Oracle--SQL行列轉換實戰OracleSQL
- sql server 行列轉換SQLServer
- Oracle中SQL語句解析的步驟(轉)OracleSQL
- (轉)隨機選擇行的SQL語句? ORACLE SQLSERVER ECT.隨機SQLOracleServer
- Oracle-行列轉換Oracle
- sql server行列轉換案例SQLServer
- 【SQL 學習】行列轉換SQL
- 【SQL】行列轉換方法示例SQL
- oracle行列轉換-行轉列Oracle
- oracle行列轉換-列轉行Oracle
- 試驗Oracle中實現行列轉換的方法（轉）Oracle
- oracle行列轉換-多行轉換成字串Oracle字串
- SQLServer資料庫管理常用的SQL和T-SQL語句SQLServer資料庫
- 常用Sqlserver中的查詢語句SQLServer
- sql 實現表的行列轉換SQL
- 轉:維護常用SQL語句收集！SQL
- Oracle 行轉列的sql語句OracleSQL
- Oracle 行列轉換 經典Oracle
- Oracle 行列轉換總結Oracle
- Oracle 行列轉換小結Oracle
- Oracle行列轉換總結Oracle
- oracle行列轉換-字串轉換成多列Oracle字串
- oracle行列轉換-多列轉換成字串Oracle字串
- oracle常用SQL語句OracleSQL
- SQLServer資料庫管理的常用SQL語句SQLServer資料庫
- 日期與字串的互相轉換SQL語句字串SQL
- ORACLE常用的SQL語法和資料物件(轉)OracleSQL物件
- Oracle 許可權常用語句【轉】Oracle
- Oracle SQL精妙SQL語句講解（轉）OracleSQL
- 行列轉換
- SQL Server中行列轉換 Pivot UnPivotSQLServer
- Oracle行列轉換及pivot子句的用法Oracle