Oracle 10g 和SQL Server 2000还没有提供专门的行列转换函数,而SQL Server 2005和Oracle 11g都可以直接用PIVOT/UNPIVOT进行方便的转换。
当时看过SQL Server 2005 Inside T SQL Querying那本书,里面讲到SQL Server 2005及以前的版本中如何实现行列转行,原书的代码如下:
原书代码
1-- Listing 6-4: Creating and Populating the Orders Table
2USE tempdb;
3GO
4IF OBJECT_ID('dbo.Orders') IS NOT NULL
5 DROP TABLE dbo.Orders;
6GO
7CREATE 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);
15CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
16 ON dbo.Orders(orderdate, orderid);
17INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
18 VALUES(30001, '20020802', 3, 'A', 10);
19INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
20 VALUES(10001, '20021224', 1, 'A', 12);
21INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
22 VALUES(10005, '20021224', 1, 'B', 20);
23INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
24 VALUES(40001, '20030109', 4, 'A', 40);
25INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
26 VALUES(10006, '20030118', 1, 'C', 14);
27INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
28 VALUES(20001, '20030212', 2, 'B', 12);
29INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
30 VALUES(40005, '20040212', 4, 'A', 10);
31INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
32 VALUES(20002, '20040216', 2, 'C', 20);
33INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
34 VALUES(30003, '20040418', 3, 'B', 15);
35INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
36 VALUES(30004, '20020418', 3, 'C', 22);
37INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
38 VALUES(30007, '20020907', 3, 'D', 30);
39GO
40-- Aggregating Data, Pre-2005 Solution, Total Qty
41SELECT 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]
45FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
46 FROM dbo.Orders) AS D
47GROUP BY custid;
48GO
49
1-- Listing 6-4: Creating and Populating the Orders Table
2USE tempdb;
3GO
4IF OBJECT_ID('dbo.Orders') IS NOT NULL
5 DROP TABLE dbo.Orders;
6GO
7CREATE 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);
15CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
16 ON dbo.Orders(orderdate, orderid);
17INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
18 VALUES(30001, '20020802', 3, 'A', 10);
19INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
20 VALUES(10001, '20021224', 1, 'A', 12);
21INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
22 VALUES(10005, '20021224', 1, 'B', 20);
23INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
24 VALUES(40001, '20030109', 4, 'A', 40);
25INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
26 VALUES(10006, '20030118', 1, 'C', 14);
27INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
28 VALUES(20001, '20030212', 2, 'B', 12);
29INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
30 VALUES(40005, '20040212', 4, 'A', 10);
31INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
32 VALUES(20002, '20040216', 2, 'C', 20);
33INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
34 VALUES(30003, '20040418', 3, 'B', 15);
35INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
36 VALUES(30004, '20020418', 3, 'C', 22);
37INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
38 VALUES(30007, '20020907', 3, 'D', 30);
39GO
40-- Aggregating Data, Pre-2005 Solution, Total Qty
41SELECT 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]
45FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
46 FROM dbo.Orders) AS D
47GROUP BY custid;
48GO
49
本人照葫芦画瓢,将上面的代码迁移到Oracle 10g中,代码如下:
迁移到Oracle中的代码
1CREATE 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
10INSERT INTO Orders
11 (orderid, orderdate, empid, custid, qty)
12VALUES
13 (10001, to_date('20021224', 'yyyymmdd'), 1, 'A', 12);
14INSERT INTO Orders
15 (orderid, orderdate, empid, custid, qty)
16VALUES
17 (10005, to_date('20021224', 'yyyymmdd'), 1, 'B', 20);
18INSERT INTO Orders
19 (orderid, orderdate, empid, custid, qty)
20VALUES
21 (40001, to_date('20030109', 'yyyymmdd'), 4, 'A', 40);
22INSERT INTO Orders
23 (orderid, orderdate, empid, custid, qty)
24VALUES
25 (10006, to_date('20030118', 'yyyymmdd'), 1, 'C', 14);
26INSERT INTO Orders
27 (orderid, orderdate, empid, custid, qty)
28VALUES
29 (20001, to_date('20030212', 'yyyymmdd'), 2, 'B', 12);
30INSERT INTO Orders
31 (orderid, orderdate, empid, custid, qty)
32VALUES
33 (40005, to_date('20040212', 'yyyymmdd'), 4, 'A', 10);
34INSERT INTO Orders
35 (orderid, orderdate, empid, custid, qty)
36VALUES
37 (20002, to_date('20040216', 'yyyymmdd'), 2, 'C', 20);
38INSERT INTO Orders
39 (orderid, orderdate, empid, custid, qty)
40VALUES
41 (30003, to_date('20040418', 'yyyymmdd'), 3, 'B', 15);
42INSERT INTO Orders
43 (orderid, orderdate, empid, custid, qty)
44VALUES
45 (30004, to_date('20020418', 'yyyymmdd'), 3, 'C', 22);
46INSERT INTO Orders
47 (orderid, orderdate, empid, custid, qty)
48VALUES
49 (30007, to_date('20020907', 'yyyymmdd'), 3, 'D', 30);
50
51SELECT 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 二零零四
55FROM (SELECT custid, to_char(orderdate,'yyyy')AS orderyear, qty
56 FROM Orders) D
57GROUP BY custid;
1CREATE 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
10INSERT INTO Orders
11 (orderid, orderdate, empid, custid, qty)
12VALUES
13 (10001, to_date('20021224', 'yyyymmdd'), 1, 'A', 12);
14INSERT INTO Orders
15 (orderid, orderdate, empid, custid, qty)
16VALUES
17 (10005, to_date('20021224', 'yyyymmdd'), 1, 'B', 20);
18INSERT INTO Orders
19 (orderid, orderdate, empid, custid, qty)
20VALUES
21 (40001, to_date('20030109', 'yyyymmdd'), 4, 'A', 40);
22INSERT INTO Orders
23 (orderid, orderdate, empid, custid, qty)
24VALUES
25 (10006, to_date('20030118', 'yyyymmdd'), 1, 'C', 14);
26INSERT INTO Orders
27 (orderid, orderdate, empid, custid, qty)
28VALUES
29 (20001, to_date('20030212', 'yyyymmdd'), 2, 'B', 12);
30INSERT INTO Orders
31 (orderid, orderdate, empid, custid, qty)
32VALUES
33 (40005, to_date('20040212', 'yyyymmdd'), 4, 'A', 10);
34INSERT INTO Orders
35 (orderid, orderdate, empid, custid, qty)
36VALUES
37 (20002, to_date('20040216', 'yyyymmdd'), 2, 'C', 20);
38INSERT INTO Orders
39 (orderid, orderdate, empid, custid, qty)
40VALUES
41 (30003, to_date('20040418', 'yyyymmdd'), 3, 'B', 15);
42INSERT INTO Orders
43 (orderid, orderdate, empid, custid, qty)
44VALUES
45 (30004, to_date('20020418', 'yyyymmdd'), 3, 'C', 22);
46INSERT INTO Orders
47 (orderid, orderdate, empid, custid, qty)
48VALUES
49 (30007, to_date('20020907', 'yyyymmdd'), 3, 'D', 30);
50
51SELECT 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 二零零四
55FROM (SELECT custid, to_char(orderdate,'yyyy')AS orderyear, qty
56 FROM Orders) D
57GROUP BY custid;
算是一种行列转行的方案吧,O(∩_∩)O~