-------------------------------成功------------------------------------------------------------
创新互联服务项目包括织金网站建设、织金网站制作、织金网页制作以及织金网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，织金网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到织金省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
with pivot_info as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (value (t), '/item/column[@name="WORKCODE"]') WORKCODE,
extractvalue (value (t), '/item/column[@name="DPAPC"]') DPAPC,
extractvalue (value (t), '/item/column[@name="FAILMODELNAME"]') FAILMODELNAME
from pivot_info,
XMLTable ('/PivotSet/item' passing WORKCODE_XML) t
-----------------半成品----------------------
with pivot_info as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (value (t), '/item/colunm[@name="WORKCODE"]') WORKCODE,
extractvalue (value (t), '/item/colunm[@name="DPAPC"]') DPAPC,
extractvalue (value (t), '/item/colunm[@name="FAILMODELNAME"]') FAILMODELNAME
from pivot_info,
XMLTable ('/PivotSet/item' passing WORKCODE_XML) t
----------------半成品---------------
with a as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (WORKCODE_XML, '/PivotSet//item/colunm[@name="WORKCODE"]/text()') WORKCODE,
extractvalue (WORKCODE_XML, '/PivotSet/item/colunm[@name="DPAPC"]/text()') DPAPC,
extractvalue (WORKCODE_XML, '/PivotSet/item/colunm[@name="FAILMODELNAME"]/text()') FAILMODELNAME
from a
------------半成品---------
select *
from (
select workcode ,DPAPC,failmodelname from(
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) ) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)) b,
xmltable('/PivotSet' passing b.workcode_xml columns
WORKCODE VARCHAR2(50) PATH
'/item/colunm[@name="WORKCODE"]',
DPAPC VARCHAR2(50) PATH
'/item/colunm[@name="DPAPC"]',
FAILMODELNAME VARCHAR2(50) PATH
'/item/colunm[@name="FAILMODELNAME"]')
--------------demo-------------
SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('
') AS B
COLUMNS USER_DEAL_A VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID[@name="AAA"]',
USER_DEAL_B VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID[@name="BBB"]',
DEAL_INURE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_INURE_TIME',
DEAL_EXPIRE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_EXPIRE_TIME',
DEAL_CREATE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_CREATE_TIME')
这里之所以非得用sql来解析xml 也是不得已。行列转换生成的Clob字段得给页面上展示 又懒得用代码解析 只想套用自己写好的展示工具。
后来折腾了一天之后 经过请教 ibatis 是个很好的工具。
然后
1.将行专列 的包含 XML 的结果 xmltype 通过 to_clob（workcode_xml） 转换成clob
2.用ibatis 配置文件 查出来转换成 XML 的 String 字符串
2.对字符串进行解析 封装grid
终于问题解决 释怀了！
写此博文 只为保留经验 以备用的时候方便查询。