|
Posted on
2009-05-12 23:11
漂泊雪狼
阅读( 599)
评论()
编辑
收藏
举报
Oracle 10g 行列转换 SQL Server 2000
Oracle 10g 和SQL Server 2000还没有提供专门的行列转换函数,而SQL Server 2005和Oracle 11g都可以直接用PIVOT/UNPIVOT进行方便的转换。
当时看过SQL Server 2005 Inside T SQL Querying那本书,里面讲到SQL Server 2005及以前的版本中如何实现行列转行,原书的代码如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif) 原书代码
1 -- Listing 6-4: Creating and Populating the Orders Table
2 USE tempdb;
3 GO
4 IF OBJECT_ID('dbo.Orders') IS NOT NULL
5 DROP TABLE dbo.Orders;
6 GO
7 CREATE TABLE dbo.Orders
8 (
9 orderid int NOT NULL PRIMARY KEY NONCLUSTERED,
10 orderdate datetime NOT NULL,
11 empid int NOT NULL,
12 custid varchar(5) NOT NULL,
13 qty int NOT NULL
14 );
15 CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
16 ON dbo.Orders(orderdate, orderid);
17 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
18 VALUES(30001, '20020802', 3, 'A', 10);
19 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
20 VALUES(10001, '20021224', 1, 'A', 12);
21 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
22 VALUES(10005, '20021224', 1, 'B', 20);
23 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
24 VALUES(40001, '20030109', 4, 'A', 40);
25 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
26 VALUES(10006, '20030118', 1, 'C', 14);
27 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
28 VALUES(20001, '20030212', 2, 'B', 12);
29 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
30 VALUES(40005, '20040212', 4, 'A', 10);
31 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
32 VALUES(20002, '20040216', 2, 'C', 20);
33 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
34 VALUES(30003, '20040418', 3, 'B', 15);
35 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
36 VALUES(30004, '20020418', 3, 'C', 22);
37 INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
38 VALUES(30007, '20020907', 3, 'D', 30);
39 GO
40 -- Aggregating Data, Pre-2005 Solution, Total Qty
41 SELECT custid,
42 SUM(CASE WHEN orderyear = 2002 THEN qty END) AS [2002],
43 SUM(CASE WHEN orderyear = 2003 THEN qty END) AS [2003],
44 SUM(CASE WHEN orderyear = 2004 THEN qty END) AS [2004]
45 FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
46 FROM dbo.Orders) AS D
47 GROUP BY custid;
48 GO
49![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
本人照葫芦画瓢,将上面的代码迁移到Oracle 10g中,代码如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif) 迁移到Oracle中的代码
1 CREATE TABLE Orders
2 (
3 orderid int NOT NULL PRIMARY KEY ,
4 orderdate date NOT NULL,
5 empid int NOT NULL,
6 custid varchar2(5) NOT NULL,
7 qty int NOT NULL
8 );
9
10 INSERT INTO Orders
11 (orderid, orderdate, empid, custid, qty)
12 VALUES
13 (10001, to_date('20021224', 'yyyymmdd'), 1, 'A', 12);
14 INSERT INTO Orders
15 (orderid, orderdate, empid, custid, qty)
16 VALUES
17 (10005, to_date('20021224', 'yyyymmdd'), 1, 'B', 20);
18 INSERT INTO Orders
19 (orderid, orderdate, empid, custid, qty)
20 VALUES
21 (40001, to_date('20030109', 'yyyymmdd'), 4, 'A', 40);
22 INSERT INTO Orders
23 (orderid, orderdate, empid, custid, qty)
24 VALUES
25 (10006, to_date('20030118', 'yyyymmdd'), 1, 'C', 14);
26 INSERT INTO Orders
27 (orderid, orderdate, empid, custid, qty)
28 VALUES
29 (20001, to_date('20030212', 'yyyymmdd'), 2, 'B', 12);
30 INSERT INTO Orders
31 (orderid, orderdate, empid, custid, qty)
32 VALUES
33 (40005, to_date('20040212', 'yyyymmdd'), 4, 'A', 10);
34 INSERT INTO Orders
35 (orderid, orderdate, empid, custid, qty)
36 VALUES
37 (20002, to_date('20040216', 'yyyymmdd'), 2, 'C', 20);
38 INSERT INTO Orders
39 (orderid, orderdate, empid, custid, qty)
40 VALUES
41 (30003, to_date('20040418', 'yyyymmdd'), 3, 'B', 15);
42 INSERT INTO Orders
43 (orderid, orderdate, empid, custid, qty)
44 VALUES
45 (30004, to_date('20020418', 'yyyymmdd'), 3, 'C', 22);
46 INSERT INTO Orders
47 (orderid, orderdate, empid, custid, qty)
48 VALUES
49 (30007, to_date('20020907', 'yyyymmdd'), 3, 'D', 30);
50
51 SELECT custid,
52 SUM(CASE WHEN orderyear = '2002' THEN qty END) as 二零零二,
53 SUM(CASE WHEN orderyear = '2003' THEN qty END)as 二零零三,
54 SUM(CASE WHEN orderyear = '2004' THEN qty END)as 二零零四
55 FROM (SELECT custid, to_char(orderdate,'yyyy')AS orderyear, qty
56 FROM Orders) D
57 GROUP BY custid;
算是一种行列转行的方案吧,O(∩_∩)O~
|