论坛上一位朋友的问题，解决方法中涉及到了列转行问题。这里记录一下。

论坛贴子地址：http://www.itpub.net/thread-1509944-1-1.html

那位朋友的问题：

----------------------------------

有两个表
表A有下列字段(可理解为每半个小时记一次数据)
yyyymmdd,V01,V02,V03,V04.......V48
表B有下列字段(可理解为开始时间和完了时间)
yyyymmdd,start,end

假设表A的数据为
20111109,1,2,3,4,5,6,….48
20111110,4,2,7,9,5,….67
表B的数据为
20111109,1,3
20111110,2,5

要求得到的数据为
20111109,6 <-1+2+3=6
20111110,23 <-2+7+9+5=23

这样的SQL可以写吗？

----------------------------------

我提供了一种解决方案

----------------------------------

先提供一种是用 列转行+COLLECTION（集合）+sum（） 的解决方法。

适用范围：8i,9i,10g及以后版本

前提：要创建一个对象和一个集合（创建脚本在后面）

另外，对于oracle 10g以后版本，还可以使用MODEL语法。用法可参考下面这个链接。

oracle 行转列列转行

下面是我测试用的脚本

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Create table

create table TBLA

(

yyyymmdd varchar2(8), -- 日期格式如20110101

V01 number, -- 时间 00:00

V02 number, -- 时间 00:30

V03 number, -- 时间 01:00

V04 number, -- 时间 01:30

V05 number, -- 时间 02:00

V06 number, -- 时间 02:30

V07 number, -- 时间 03:00

V08 number, -- 时间 03:30

V09 number, -- 时间 04:00

V10 number, -- 时间 04:30

V11 number, -- 时间 05:00

V12 number, -- 时间 05:30

V13 number, -- 时间 06:00

V14 number, -- 时间 06:30

V15 number, -- 时间 07:00

V16 number, -- 时间 07:30

V17 number, -- 时间 08:00

V18 number, -- 时间 08:30

V19 number, -- 时间 09:00

V20 number, -- 时间 09:30

V21 number, -- 时间 10:00

V22 number, -- 时间 10:30

V23 number, -- 时间 11:00

V24 number, -- 时间 11:30

V25 number, -- 时间 12:00

V26 number, -- 时间 12:30

V27 number, -- 时间 13:00

V28 number, -- 时间 13:30

V29 number, -- 时间 14:00

V30 number, -- 时间 14:30

V31 number, -- 时间 15:00

V32 number, -- 时间 15:30

V33 number, -- 时间 16:00

V34 number, -- 时间 16:30

V35 number, -- 时间 17:00

V36 number, -- 时间 17:30

V37 number, -- 时间 18:00

V38 number, -- 时间 18:30

V39 number, -- 时间 19:00

V40 number, -- 时间 19:30

V41 number, -- 时间 20:00

V42 number, -- 时间 20:30

V43 number, -- 时间 21:00

V44 number, -- 时间 21:30

V45 number, -- 时间 22:00

V46 number, -- 时间 22:30

V47 number, -- 时间 23:00

V48 number -- 时间 23:30

);

-- Create table

create table TBLB

(

yyyymmdd varchar2(8), -- 日期格式如20110101

startidx number, -- 记录处理用对应的tbla中的起始字段索引如1→V01 2→V02 。。。。48→V48

endidx number -- 记录处理用对应的tbla中的起结束字段索引如1→V01 2→V02 。。。。48→V48

);

insert into TBLA (YYYYMMDD, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11, V12, V13, V14, V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, V29, V30, V31, V32, V33, V34, V35, V36, V37, V38, V39, V40, V41, V42, V43, V44, V45, V46, V47, V48)

values ('20111109', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240, 250, 260, 270, 280, 290, 300, 310, 320, 330, 340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450, 460, 470, 480);

insert into TBLA (YYYYMMDD, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11, V12, V13, V14, V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, V29, V30, V31, V32, V33, V34, V35, V36, V37, V38, V39, V40, V41, V42, V43, V44, V45, V46, V47, V48)

values ('20111110', 11, 21, 31, 41, 51, 61, 71, 81, 91, 101, 111, 121, 131, 141, 151, 161, 171, 181, 191, 201, 211, 221, 231, 241, 251, 261, 271, 281, 291, 301, 311, 321, 331, 341, 351, 361, 371, 381, 391, 401, 411, 421, 431, 441, 451, 461, 471, 481);

commit;

insert into TBLB (YYYYMMDD, STARTIDX, ENDIDX)

values ('20111109', 1, 3);

insert into TBLB (YYYYMMDD, STARTIDX, ENDIDX)

values ('20111110', 2, 5);

commit;

-- TBLA 中的数据

select YYYYMMDD,V01,V02,V03,V04,V05,V06 from tbla;

-- TBLB 中的数据

select * from tblb;

-- 我们先手工算一下预想结果

20111109 的V01+V02+V03=60

20111110 的V02+03+V04+V05=144

-- 执行我们写的SQL文，生成结果。

-- 创建一个对象和一个集合：

-- obj_tbla对象，存放V01~V48

-- drop type obj_tbla;

CREATE TYPE obj_tbla AS OBJECT(

COL_NAME VARCHAR2(40), --转换前列的列名(如：V01,V02.....)

COL_IDX number, --转换前列IDX(规则：V01→1,V02→2，.....)

COL_VAL number --转换前列的值

);

/

-- obj_tbla对象的集合

-- drop type arr_tbla;

CREATE TYPE arr_tbla AS VARRAY(48) OF obj_tbla;

/

-- 执行的SQL文

with tmp as

(

SELECT YYYYMMDD, t.COL_NAME AS COL_NAME, t.COL_IDX AS COL_IDX,t.COL_VAL AS COL_VAL

FROM tbla,

TABLE(

arr_tbla(

obj_tbla('V01',1,tbla.V01),

obj_tbla('V02',2,tbla.V02),

obj_tbla('V03',3,tbla.V03),

obj_tbla('V04',4,tbla.V04),

obj_tbla('V05',5,tbla.V05),

obj_tbla('V06',6,tbla.V06),

obj_tbla('V07',7,tbla.V07),

obj_tbla('V08',8,tbla.V08),

obj_tbla('V09',9,tbla.V09),

obj_tbla('V10',10,tbla.V10),

obj_tbla('V11',11,tbla.V11),

obj_tbla('V12',12,tbla.V12),

obj_tbla('V13',13,tbla.V13),

obj_tbla('V14',14,tbla.V14),

obj_tbla('V15',15,tbla.V15),

obj_tbla('V16',16,tbla.V16),

obj_tbla('V17',17,tbla.V17),

obj_tbla('V18',18,tbla.V18),

obj_tbla('V19',19,tbla.V19),

obj_tbla('V20',20,tbla.V20),

obj_tbla('V21',21,tbla.V21),

obj_tbla('V22',22,tbla.V22),

obj_tbla('V23',23,tbla.V23),

obj_tbla('V24',24,tbla.V24),

obj_tbla('V25',25,tbla.V25),

obj_tbla('V26',26,tbla.V26),

obj_tbla('V27',27,tbla.V27),

obj_tbla('V28',28,tbla.V28),

obj_tbla('V29',29,tbla.V29),

obj_tbla('V30',30,tbla.V30),

obj_tbla('V31',31,tbla.V31),

obj_tbla('V32',32,tbla.V32),

obj_tbla('V33',33,tbla.V33),

obj_tbla('V34',34,tbla.V34),

obj_tbla('V35',35,tbla.V35),

obj_tbla('V36',36,tbla.V36),

obj_tbla('V37',37,tbla.V37),

obj_tbla('V38',38,tbla.V38),

obj_tbla('V39',39,tbla.V39),

obj_tbla('V40',40,tbla.V40),

obj_tbla('V41',41,tbla.V41),

obj_tbla('V42',42,tbla.V42),

obj_tbla('V43',43,tbla.V43),

obj_tbla('V44',44,tbla.V44),

obj_tbla('V45',45,tbla.V45),

obj_tbla('V46',46,tbla.V46),

obj_tbla('V47',47,tbla.V47),

obj_tbla('V48',48,tbla.V48)

)

) t

ORDER BY 1, 2,3,4

)

select tblb.YYYYMMDD,sum(tmp.COL_VAL) from tblb,tmp

where tblb.YYYYMMDD = tmp.YYYYMMDD

and tblb.startidx <=tmp.COL_IDX and tmp.COL_IDX <=tblb.endidx

group by tblb.YYYYMMDD;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

下面是执行结果。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

■创建TBLA、TBLB表，并插入数据。

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> set linesize 8000

SQL> set pagesize 8000

SQL>

SQL> -- Create table

SQL> create table TBLA

2 (

3 yyyymmdd varchar2(8), -- 日期格式如20110101

4 V01 number, -- 时间 00:00

5 V02 number, -- 时间 00:30

6 V03 number, -- 时间 01:00

7 V04 number, -- 时间 01:30

8 V05 number, -- 时间 02:00

9 V06 number, -- 时间 02:30

10 V07 number, -- 时间 03:00

11 V08 number, -- 时间 03:30

12 V09 number, -- 时间 04:00

13 V10 number, -- 时间 04:30

14 V11 number, -- 时间 05:00

15 V12 number, -- 时间 05:30

16 V13 number, -- 时间 06:00

17 V14 number, -- 时间 06:30

18 V15 number, -- 时间 07:00

19 V16 number, -- 时间 07:30

20 V17 number, -- 时间 08:00

21 V18 number, -- 时间 08:30

22 V19 number, -- 时间 09:00

23 V20 number, -- 时间 09:30

24 V21 number, -- 时间 10:00

25 V22 number, -- 时间 10:30

26 V23 number, -- 时间 11:00

27 V24 number, -- 时间 11:30

28 V25 number, -- 时间 12:00

29 V26 number, -- 时间 12:30

30 V27 number, -- 时间 13:00

31 V28 number, -- 时间 13:30

32 V29 number, -- 时间 14:00

33 V30 number, -- 时间 14:30

34 V31 number, -- 时间 15:00

35 V32 number, -- 时间 15:30

36 V33 number, -- 时间 16:00

37 V34 number, -- 时间 16:30

38 V35 number, -- 时间 17:00

39 V36 number, -- 时间 17:30

40 V37 number, -- 时间 18:00

41 V38 number, -- 时间 18:30

42 V39 number, -- 时间 19:00

43 V40 number, -- 时间 19:30

44 V41 number, -- 时间 20:00

45 V42 number, -- 时间 20:30

46 V43 number, -- 时间 21:00

47 V44 number, -- 时间 21:30

48 V45 number, -- 时间 22:00

49 V46 number, -- 时间 22:30

50 V47 number, -- 时间 23:00

51 V48 number -- 时间 23:30

52 );

Table created

SQL> -- Create table

SQL> create table TBLB

2 (

3 yyyymmdd varchar2(8), -- 日期格式如20110101

4 startidx number, -- 记录处理用对应的tbla中的起始字段索引如1→V01 2→V02 。。。。48→V48

5 endidx number -- 记录处理用对应的tbla中的起结束字段索引如1→V01 2→V02 。。。。48→V48

6 );

Table created

SQL> insert into TBLA (YYYYMMDD, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11, V12, V13, V14, V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, V29, V30, V31, V32, V33, V34, V35, V36, V37, V38, V39, V40, V41, V42, V43, V44, V45, V46, V47, V48)

2 values ('20111109', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 240, 250, 260, 270, 280, 290, 300, 310, 320, 330, 340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450, 460, 470, 480);

1 row inserted

SQL> insert into TBLA (YYYYMMDD, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11, V12, V13, V14, V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, V29, V30, V31, V32, V33, V34, V35, V36, V37, V38, V39, V40, V41, V42, V43, V44, V45, V46, V47, V48)

2 values ('20111110', 11, 21, 31, 41, 51, 61, 71, 81, 91, 101, 111, 121, 131, 141, 151, 161, 171, 181, 191, 201, 211, 221, 231, 241, 251, 261, 271, 281, 291, 301, 311, 321, 331, 341, 351, 361, 371, 381, 391, 401, 411, 421, 431, 441, 451, 461, 471, 481);

1 row inserted

SQL> commit;

Commit complete

SQL> insert into TBLB (YYYYMMDD, STARTIDX, ENDIDX)

2 values ('20111109', 1, 3);

1 row inserted

SQL> insert into TBLB (YYYYMMDD, STARTIDX, ENDIDX)

2 values ('20111110', 2, 5);

1 row inserted

SQL> commit;

Commit complete

■查看TBLA、TBLB中的数据

SQL> select YYYYMMDD,V01,V02,V03,V04,V05,V06 from tbla;

YYYYMMDD V01 V02 V03 V04 V05 V06

-------- ---------- ---------- ---------- ---------- ---------- ----------

20111109 10 20 30 40 50 60

20111110 11 21 31 41 51 61

SQL> select * from tblb;

YYYYMMDD STARTIDX ENDIDX

-------- ---------- ----------

20111109 1 3

20111110 2 5

SQL>

■我们先手工算一下预想结果

20111109 的V01+V02+V03=10+20+30=60

20111110 的V02+03+V04+V05=21+31+41+51=144

■执行我们写的SQL文，生成结果。

■■创建一个对象和一个集合：

SQL> -- obj_tbla对象，存放V01~V48

SQL> -- drop type obj_tbla;

SQL> CREATE TYPE obj_tbla AS OBJECT(

2 COL_NAME VARCHAR2(40), --转换前列的列名(如：V01,V02.....)

3 COL_IDX number, --转换前列IDX(规则：V01→1,V02→2，.....)

4 COL_VAL number --转换前列的值

5 );

6 /

Type created

SQL> -- obj_tbla对象的集合

SQL> -- drop type arr_tbla;

SQL> CREATE TYPE arr_tbla AS VARRAY(48) OF obj_tbla;

2 /

Type created

SQL>

■■执行SQL文

SQL> with tmp as

2 (

3 SELECT YYYYMMDD, t.COL_NAME AS COL_NAME, t.COL_IDX AS COL_IDX,t.COL_VAL AS COL_VAL

4 FROM tbla,

5 TABLE(

6 arr_tbla(

7 obj_tbla('V01',1,tbla.V01),

8 obj_tbla('V02',2,tbla.V02),

9 obj_tbla('V03',3,tbla.V03),

10 obj_tbla('V04',4,tbla.V04),

11 obj_tbla('V05',5,tbla.V05),

12 obj_tbla('V06',6,tbla.V06),

13 obj_tbla('V07',7,tbla.V07),

14 obj_tbla('V08',8,tbla.V08),

15 obj_tbla('V09',9,tbla.V09),

16 obj_tbla('V10',10,tbla.V10),

17 obj_tbla('V11',11,tbla.V11),

18 obj_tbla('V12',12,tbla.V12),

19 obj_tbla('V13',13,tbla.V13),

20 obj_tbla('V14',14,tbla.V14),

21 obj_tbla('V15',15,tbla.V15),

22 obj_tbla('V16',16,tbla.V16),

23 obj_tbla('V17',17,tbla.V17),

24 obj_tbla('V18',18,tbla.V18),

25 obj_tbla('V19',19,tbla.V19),

26 obj_tbla('V20',20,tbla.V20),

27 obj_tbla('V21',21,tbla.V21),

28 obj_tbla('V22',22,tbla.V22),

29 obj_tbla('V23',23,tbla.V23),

30 obj_tbla('V24',24,tbla.V24),

31 obj_tbla('V25',25,tbla.V25),

32 obj_tbla('V26',26,tbla.V26),

33 obj_tbla('V27',27,tbla.V27),

34 obj_tbla('V28',28,tbla.V28),

35 obj_tbla('V29',29,tbla.V29),

36 obj_tbla('V30',30,tbla.V30),

37 obj_tbla('V31',31,tbla.V31),

38 obj_tbla('V32',32,tbla.V32),

39 obj_tbla('V33',33,tbla.V33),

40 obj_tbla('V34',34,tbla.V34),

41 obj_tbla('V35',35,tbla.V35),

42 obj_tbla('V36',36,tbla.V36),

43 obj_tbla('V37',37,tbla.V37),

44 obj_tbla('V38',38,tbla.V38),

45 obj_tbla('V39',39,tbla.V39),

46 obj_tbla('V40',40,tbla.V40),

47 obj_tbla('V41',41,tbla.V41),

48 obj_tbla('V42',42,tbla.V42),

49 obj_tbla('V43',43,tbla.V43),

50 obj_tbla('V44',44,tbla.V44),

51 obj_tbla('V45',45,tbla.V45),

52 obj_tbla('V46',46,tbla.V46),

53 obj_tbla('V47',47,tbla.V47),

54 obj_tbla('V48',48,tbla.V48)

55 )

56 ) t

57 ORDER BY 1, 2,3,4

58 )

59 select tblb.YYYYMMDD,sum(tmp.COL_VAL) from tblb,tmp

60 where tblb.YYYYMMDD = tmp.YYYYMMDD

61 and tblb.startidx <=tmp.COL_IDX and tmp.COL_IDX <=tblb.endidx

62 group by tblb.YYYYMMDD;

YYYYMMDD SUM(TMP.COL_VAL)

-------- ----------------

20111109 60

20111110 144

SQL>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----------------------------------

以上。

ITPub博客

【SQL】列转行方法实践

最新文章