Shulou(Shulou.com)06/01 Report--

-success-

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

Semi-finished products--

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

-semi-finished products-

With an 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

-semi-finished products-

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 ('$Bash delimited basic PASSING XMLTYPE.

A

B

C

D

E

one

two

three

four

five

six

seven

eight

nine

ten

') 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')

The reason why we have to use sql to parse xml is also a last resort. The Clob field generated by the row-column transformation has to be displayed on the page and doesn't bother to use code parsing. I just want to apply the presentation tool I have written.

Later, after a day of trouble, after consulting ibatis is a very good tool.

And then

1. Convert the result xmltype containing XML of the row column to clob through to_clob (workcode_xml)

two。 Use the ibatis configuration file to find out the String string converted to XML

two。 Parse the string and encapsulate grid

Finally, the problem is solved and relieved!

Write this blog post only to keep the experience and make it easy to query when you have a spare time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.