oracle和sqlserver转换,《转》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