客户id 商品编码
0001 01000111
0001 00000113
0001 00000112
0002 00200121
0002 00200122
0003 00000123
0003 0000 131
……. ………..
客户id 商品编码
0001 01000111, 00000112, 00000113
0002 00200121,00200122
0003 00000131
…… ……………………
0001 01000111
0001 00000113
0001 00000112
0002 00200121
0002 00200122
0003 00000123
0003 0000 131
……. ………..
客户id 商品编码
0001 01000111, 00000112, 00000113
0002 00200121,00200122
0003 00000131
…… ……………………
select '0001' a,'01000111' b from dual
union all
select '0001' a,'00000113' b from dual
union all
select '0001' a,'00000112' b from dual
union all
select '0002' a,'00200121' b from dual
union all
select '0002' a,'00200122' b from dual
union all
select '0003' a,'00000123' b from dual
union all
select '0003' a,'00000131' b from dual
)
select a,wm_concat(b) from temp group by a
--没看出你B的连接顺序!
--如果你要求你B字段按顺序连接,可以这样:select a,Max(b) b from(
select a,wm_concat(b)over(partition by a order by b ) b from temp
)
group by a
-- oracle 10
select "客户id",wm_concat("商品编码") "商品编码" from tb group by "客户id";-- oracle 9
select "客户id", ltrim(max(sys_connect_by_path("商品编码",',')),',') "商品编码"
from (select "客户id","商品编码",
row_number() over (partition by "客户id" order by "商品编码") rn
from tb)
group by "客户id"
start with rn=1
connect by prior "客户id"="客户id" and prior rn=rn-1;
讲解下oracle 9 的过程
楼猪试一下