记录格式请教

Question

我有一个表a
如下表
itemid typeid typename
1       1       aaa
1       2       bbb
2       3       ccc
2       5       ddd
3       5       ddd表b如下表
itemid   itemname
1           kkkk
2           llll
3           ooooa表同b表通过itemid 关联，取出下表记录格式
itemid   itemname   typename
1           kkkk     aaa,bbb
2           llll     ccc,ddd
3           oooo     ddd请高手指教如何才能得到这个结果格式的SQL语句，多谢！

Answer 1

-- 用列转行函数wmsys.wm_concat

Answer 2

如果是10g或者以上版本的话可以用wm_concat()
select itemid,itemname,wm_concat(typename) typename
from a,b
where a.itemid=b.itemid
group by itemid,itemname

Answer 3

SQL> select * from a;
/*
                                 ITEMID                                  TYPEID TYPENAME
--------------------------------------- --------------------------------------- ----------
                                      1                                       2 bbb
                                      2                                       3 ccc
                                      2                                       5 ddd
                                      3                                       5 ddd
                                      1                                       1 aaa
*/
SQL> select * from b;
/*
                                 ITEMID ITEMNAME
--------------------------------------- ----------
                                      1 kkk
                                      2 lll
                                      3 ooo
*/
SQL> select a.itemid,b.itemname,wmsys.wm_concat(a.typename)
  2  from a,b
  3  where a.itemid=b.itemid
  4  group by a.itemid,b.itemname;
/*
                                 ITEMID ITEMNAME   WMSYS.WM_CONCAT(A.TYPENAME)
--------------------------------------- ---------- --------------------------------------------------------------------------------
                                      1 kkk        bbb,aaa
                                      2 lll        ccc,ddd
                                      3 ooo        ddd
*/