如何通过sql语句把图一变成图二。 create table TEST
(
XH VARCHAR2(100),
XM_CODE VARCHAR2(100),
XM_NAME VARCHAR2(100),
XM_BZ VARCHAR2(200),
DIV_CODE VARCHAR2(100)
);
insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目1', '001', '项目测试1', '备注1', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目2', '002', '项目测试2', '备注2', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目3', '003', '项目测试3', '备注3', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目4', '004', '项目测试4', '备注4', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目5', '005', '项目测试5', '备注5', '001001');
commit;
(
XH VARCHAR2(100),
XM_CODE VARCHAR2(100),
XM_NAME VARCHAR2(100),
XM_BZ VARCHAR2(200),
DIV_CODE VARCHAR2(100)
);
insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目1', '001', '项目测试1', '备注1', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目2', '002', '项目测试2', '备注2', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目3', '003', '项目测试3', '备注3', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目4', '004', '项目测试4', '备注4', '001001'); insert into test (XH, XM_CODE, XM_NAME, XM_BZ, DIV_CODE)
values ('项目5', '005', '项目测试5', '备注5', '001001');
commit;
'||xm_bz),',','
')bz from test
group by div_code
'||xm_bz||'#'),',','
'), '#', char(10)) bz from test
group by div_code
执行时报:ora-00936缺少表达式 错误
执行时报:ora-00904 wm_concat 无效的标识符错误。
你的数据库版本低,wm_concat要10g支持。这里你可以参考一下这个方式,写个自己的聚合函数http://blog.csdn.net/inthirties/archive/2009/10/10/4652245.aspxhttp://www.inthirties.com/thread-250-1-1.html
楼主可以自己写个函数代替,若是9i的也可以用sys_connect_by_path来转换,但是表大的话效率较低
http://topic.csdn.net/u/20090910/21/5A7D78B3-0696-4EF1-9BAC-3B67DF48A5F6.html
select DIV_CODE,replace(bb,',',chr(10)) from
(
select DIV_CODE,substr(max(sys_connect_by_path(aa,',')),2) bb
from
(
select xh||':'||xm_name||'
'||xm_bz aa,DIV_CODE
,row_number() over(order by 1) rn from test
)
start with rn=1 connect by prior rn=rn-1
group by div_code
)--RESULT:001001 项目1:项目测试1
备注1
项目2:项目测试2
备注2
项目3:项目测试3
备注3
项目4:项目测试4
备注4
项目5:项目测试5
备注5