函数解连接字符串.txt

SQL语言常用的一些命令各代码

/***********题目

A1  A2

a   b

a   cb   cb   d
怎么把它变成这样啊

A1   A2

a    b(c)

b    c(d)
**************/
/*测试表*/
create table a(A1 varchar(1),A2 varchar(50))insert into a select 'a','b'

insert into a select 'a','c'
insert into a select 'a','d'

insert into a select 'b','c'
insert into a select 'b','d'/*函数(如果行记录不重复）*/create function char_add(@aa varchar(1),@bb varchar(3))

returns varchar(50)

asbegin

declare @a int,@b varchar(50),@c int,@d varchar(50)select @a=1
select @b=min(A2) from a where A1=@aa
select @d=@b
select @c=count(0) from a where A1=@aa

while @a<@c

begin
select @a=@a+1
select @b=min(A2) from a where A1=@aa and A2>@b
select @d=@d+'('+@b+')'

end
return @d

end
/*查询*/

select distinct A1,dbo.char_add(A1,A2) as A2 from a