Oracle、Kettle、SQL

karsong 发布于 2012/06/19 10:19
阅读 991
收藏 0
有表A和C,C有多条记录。 把C表的多条记录用XMLELEMENT包装成xml,作为A表的一个字段,sql怎么写? 先谢了
表A:
      id  clobs
      1   
表C:id  content
         1    AAAA
         1    BBBB
         2    CCCC
通过id把表C的两条记录变成
<?xml version="1.0" encoding="UTF-8"?>
<name>
<node>AAAA<node>
<node>BBBB<node>
<name>   
存到表A的clobs字段里边
加载中
0
Y-QTCe
Y-QTCe

Oracle也有个类似的wm_concat,不过好像不是所有版本都有。你得先试试。

 

 

  WITH a AS(

 SELECT '1' AS ID, 'AAAA' AS content FROM dual

UNION ALL

 SELECT '1' AS ID,'BBBB' AS content FROM dual

UNION ALL

 SELECT '2' AS ID, 'CCCC' AS content FROM dual

)

SELECT ID ,wm_concat(content)  FROM a GROUP BY ID


karsong
karsong
感谢!!
0
Y-QTCe
Y-QTCe

抛砖引玉,给个繁琐的:

SELECT ID,clobs FROM (

SELECT  ID,level,sys_connect_by_path(content,' ') clobs FROM (

  WITH a AS(

 SELECT '1' AS ID, 'AAAA' AS content FROM dual

UNION ALL

 SELECT '1' AS ID,'BBBB' AS content FROM dual

UNION ALL

 SELECT '2' AS ID, 'CCCC' AS content FROM dual

)

SELECT ID ,ROWNUM rn,content  FROM a

WHERE ID='1'

) a

START WITH a.rn = 1

CONNECT BY PRIOR a.rn = a.rn-1

ORDER BY LEVEL DESC

) a

WHERE ROWNUM = 1


0
Y-QTCe
Y-QTCe
mysql 的话貌似可以用group_concat
返回顶部
顶部