--求一SQL语句(例转行)--

[luoyoumou]

--有表TB，有两个字段，测试数据如下
CreateDay cnt

2009-12-01 (A1-16)
2009-12-01 (A4-3)
2009-12-01 (B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)
2009-12-17 (B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)
2010-01-19 (B5-7)
2010-01-20 (A1-5)
2010-01-20 (B5-2)
2010-01-20 (B6-1)

-----------------------------
-- 如何将其查询，使之生成结果如下：??

CreateDay cnt

2009-12-01 (A1-16)#(A4-3)#(B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)#(B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)#(B5-7)
2010-01-20 (A1-5)#(B5-2)#(B6-1)

[suiziguo]

selectg CreateDay,relpace(wm_concat(cnt),',','#') from tb;

[suiziguo]

多了个g。。。。。


select CreateDay,relpace(wm_concat(cnt),',','#') from tb;

[YY_MM_DD]

select  CreateDay,regexp_relpace(wm_concat(cnt),',','#') from tb;

[luoyoumou]

-- 呵呵：谢谢各位：正确答案：
create table tb(createtime varchar(20),cnt varchar(100));

insert into tb(createtime,cnt)
values('2009-12-01','(A1-16)');
insert into tb(createtime,cnt)
values('2009-12-01','(A4-3)');
insert into tb(createtime,cnt)
values('2009-12-01','(B5-2)');
insert into tb(createtime,cnt)
values('2009-12-02','(A1-6)');
insert into tb(createtime,cnt)
values('2009-12-14','(A1-1)');
insert into tb(createtime,cnt)
values('2009-12-17','(A5-1)');
insert into tb(createtime,cnt)
values('2009-12-17','(B5-13)');
insert into tb(createtime,cnt)
values('2009-12-21','(A1-1)');
insert into tb(createtime,cnt)
values('2010-01-19','(B5-7)');
insert into tb(createtime,cnt)
values('2010-01-20','(A1-5)');
insert into tb(createtime,cnt)
values('2010-01-20','(B5-2)');
insert into tb(createtime,cnt)
values('2010-01-20','(B6-1)');


select Createtime,regexp_replace(wm_concat(cnt),',','#') as cnt from tb
group by createtime;

[Phoenix_99]

selectg CreateDay,relpace(wm_concat(cnt),',','#') from tb;

[luoyoumou]

引用 12 楼 xiaoheixiaobai 的回复:

select  CreateDay,regexp_relpace(wm_concat(cnt),',','#') from tb;

如果cnt里面本身含有“,”字符，那么relpace之后，岂不是把原有的“,”号变成"#"了吗，那应该怎么办呢？

--呵呵：真细心，但：cnt里面本身不会含有“,”字符

[tiantom]

select t.createtime, replace(wm_concat(t.cnt), ',', '#')
from tb t
group by t.createtime

[爱吃芒果的猴子]

select  Createtime,regexp_replace(wm_concat(cnt),',','#') as cnt from tb 
group by createtime; 

[cargoj]

可以用分析函数来完成
c1,c2两列，先用GROUP BY以后COUNT（×）来获取最大分组行数

Select c1
max(decode(rn,1,c2,null)) c2_1,
max(decode(rn,2,c2,null)) c2_2,
…
max(decode(rn,N,c2,null)) c2_N
from (select c1, c2
row_number() over (partition by C1
order by <something>)
rn from T
<some predicate>)
group by C1

[爱吃芒果的猴子]

regexp_replace的意义是找到于给定模式匹配的字符串并用其他的字符串来替代。
其原型是：regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])
每个参数的意思分别是：
x 待匹配的函数
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串
start 开始位置
occurence　匹配次数
match_option　匹配参数

[xiaoheixiaobai]

学习了，谢谢。。。。。。。。

[碧水幽幽泉]

引用 7 楼 bobo_916 的回复:

SQL codeselect Createtime,regexp_replace(wm_concat(cnt),',','#')as cntfrom tbgroupby createtime;

路过！

[xiaoheixiaobai]

select CreateDay,regexp_relpace(wm_concat(cnt),',','#') from tb;

如果cnt里面本身含有“,”字符，那么relpace之后，岂不是把原有的“,”号变成"#"了吗，那应该怎么办呢？