现有表记录如下;
fcoding Fmemo
01 zhang
01 wang
02 li
02 lili
02 feifei
03.....想要的结果是
fcoding Fmemo
01 zhang;wang;
02 li;lili;feifei;
fcoding Fmemo
01 zhang
01 wang
02 li
02 lili
02 feifei
03.....想要的结果是
fcoding Fmemo
01 zhang;wang;
02 li;lili;feifei;
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+[text]+';' from 表 where fcoding=@fcoding
return @str
endGOselect fcoding,dbo.f_str(fcoding ) as Fmemo
from A
group by fcoding
insert T select '01', 'zhang'
union all select '01', 'wang'
union all select '02', 'li'
union all select '02', 'lili'
union all select '02', 'feifei'create function fun(@fcoding varchar(10))
returns varchar(200)
as
begin
declare @re varchar(200)
set @re=''
select @re=@re+';'+Fmemo from T where fcoding=@fcoding return stuff(@re, 1, 1, '')
endselect fcoding, Fmemo=dbo.fun(fcoding) from T group by fcoding--result
fcoding Fmemo
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 zhang;wang
02 li;lili;feifei(2 row(s) affected)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+[fcoding]+';' from 表 where fcoding=@fcoding
return @str
endGOselect fcoding,dbo.f_str(fcoding) as Fmemo
from 表
group by fcoding
1,就是上面两位使用的函数方式;
2,使用树形遍历的方式;他给了个oracle的示例;回头哪个兄弟给翻译一下
3,是建立临时表,然后update;
4,使用游标;
后两种暂不考虑;第一种两位给出了答案;哪个能帮忙翻译一下下面这种方式。SQL> create table t(col1 varchar2(10),col2 varchar2(10));
表已创建。
SQL>
SQL> insert into t values('001','vl1');
已创建 1 行。
SQL> insert into t values('001','vl2');
已创建 1 行。
SQL> insert into t values('001','vl3');
已创建 1 行。
SQL> insert into t values('002','vl1');
已创建 1 行。
SQL> insert into t values('002','vl2');
已创建 1 行。
SQL> insert into t values('002','vl3');
已创建 1 行。
SQL> insert into t values('002','vl4');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> select * from t;
COL1 COL2
---------- ----------
001 vl1
001 vl2
001 vl3
002 vl1
002 vl2
002 vl3
002 vl4
已选择7行。
SQL> COL COL2 FORMAT A20
SQL> SELECT COL1,LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2,',')),',') COL2
2 FROM
3 (
4 SELECT COL1,COL2,MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
5 (ROW_NUMBER() OVER(ORDER BY COL1,COL2))+(DENSE_RANK() OVER (ORDER BY COL1)) NUMID
6 FROM T
7 )
8 START WITH COL2=COL2_MIN CONNECT BY NUMID-1=PRIOR NUMID
9 GROUP BY COL1;
COL1 COL2
---------- --------------------
001 vl1,vl2,vl3
002 vl1,vl2,vl3,vl4
create table T(fcoding varchar(10), Fmemo varchar(10))
insert T select '01', 'zhang'
union all select '01', 'wang'
union all select '02', 'li'
union all select '02', 'lili'
union all select '02', 'feifei'select fcoding, cast(Fmemo as varchar(200)) as Fmemo into #T from T order by fcodingdeclare @fcoding varchar(10), @Fmemo varchar(200)
update #T set
@Fmemo=case when fcoding=@fcoding then @Fmemo+';'+Fmemo else Fmemo end,
@fcoding=fcoding,
Fmemo=@Fmemoselect fcoding, Fmemo=max(Fmemo) from #T group by fcoding--result
fcoding Fmemo
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 zhang;wang
02 li;lili;feifei(2 row(s) affected)
create table T(fcoding varchar(10), Fmemo varchar(10))
insert T select '01', 'zhang'
union all select '01', 'wang'
union all select '02', 'li'
union all select '02', 'lili'
union all select '02', 'feifei'declare @t table(fcoding varchar(10), Fmemo varchar(200))
declare cur cursor local for
select fcoding, Fmemo from T order by fcodingdeclare @fcoding_old varchar(10), @fcoding varchar(10), @Fmemo varchar(10), @str varchar(200)open curfetch cur into @fcoding, @Fmemo
select @fcoding_old=@fcoding, @str=''
while @@fetch_status=0
begin
if @fcoding_old=@fcoding
select @str=@str+';'+@Fmemo
else
begin
insert @t values(@fcoding_old, stuff(@str, 1, 1, ''))
select @str=@Fmemo, @fcoding_old=@fcoding
end fetch cur into @fcoding, @Fmemo
endinsert @t values(@fcoding_old, stuff(@str, 1, 1, ''))
close cur
deallocate cur--
select * from @t
create table T(fcoding varchar(10), Fmemo varchar(10))
insert T select '01', 'zhang'
union all select '01', 'wang'
union all select '02', 'li'
union all select '02', 'lili'
union all select '02', 'feifei'declare @t table(fcoding varchar(10), Fmemo varchar(200))
declare cur cursor local for
select fcoding, Fmemo from T order by fcodingdeclare @fcoding_old varchar(10), @fcoding varchar(10), @Fmemo varchar(10), @str varchar(200)open curfetch cur into @fcoding, @Fmemo
select @fcoding_old=@fcoding, @str=''
while @@fetch_status=0
begin
if @fcoding_old=@fcoding
select @str=@str+';'+@Fmemo
else
begin
insert @t values(@fcoding_old, stuff(@str, 1, 1, ''))
select @str=';'+@Fmemo, @fcoding_old=@fcoding
end fetch cur into @fcoding, @Fmemo
endinsert @t values(@fcoding_old, stuff(@str, 1, 1, ''))
close cur
deallocate cur--
select * from @t--result
fcoding Fmemo
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 zhang;wang
02 li;lili;feifei(2 row(s) affected)
兄弟辛苦了;
哥们上面已经说了;不考虑后两种方式;
想看看第二种方式在sqlserver下的实现。