--建函数
Create function F_Getstr(@num varchar(10))
retruns varchar(2000)
as
begin
Declare @s varchar(2000)
set @s=''
select @s=@s+','+string+'' from 表1 where num=@num
set @s=stuff(@s,1,1)
return @s
end--查询
select num,string=dbo.F_Getstr(num)
from 表1
group by num
Create function F_Getstr(@num varchar(10))
retruns varchar(2000)
as
begin
Declare @s varchar(2000)
set @s=''
select @s=@s+','+string+'' from 表1 where num=@num
set @s=stuff(@s,1,1)
return @s
end--查询
select num,string=dbo.F_Getstr(num)
from 表1
group by num
在 'retruns' 附近有语法错误。
服务器: 消息 174,级别 15,状态 1,过程 F_Getstr,行 8
stuff 函数要求有 4 个参数。
create table 表1(id int identity(1,1),num varchar(10),string varchar(10))
insert into 表1 select 'G123','abc'
union all select 'H456','abc'
union all select 'G123','abc'
union all select 'K789','xyz'
union all select 'H456','xyz'
union all select 'K789','xyz'
--函数
Create function F_Getstr(@num varchar(10))
returns varchar(2000)
as
begin
Declare @s varchar(2000)
set @s=''
select @s=@s+','+T.string+'' from (select distinct string from 表1 where num=@num) T
set @s=stuff(@s,1,1,'')
return @s
end--查询select
num,string=dbo.F_Getstr(num)
from 表1 A
group by num--结果
num string
----- -----------
G123 abc
H456 abc,xyz
K789 xyz
--查询
select ID=(select sum(1) from
(select
num,string=dbo.F_Getstr(num)
from 表1
group by num
) T where T.num+'_'+T.string<=A.num+'_'+A.string)
,* from (select
num,string=dbo.F_Getstr(num)
from 表1
group by num
) A--结果
line num string
-------- ------ ---------
1 G123 abc
2 H456 abc,xyz
3 K789 xyz删除测试环境
Drop table 表1
Drop Function F_Getstr
什么表结构啊??
结构是这样的
id bigint 8 标识
num varchar 50
string varchar 50再帮帮忙吧^^
--那就更简单了!insert table2
select
num,string=dbo.F_Getstr(num)
from 表1
group by num
仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'table2' 中为标识列指定显式值。
create table #T(id bigint identity(1,1),
num varchar(50),
string varchar(50)
)insert into #T(num,string)
select
num,
string=dbo.F_Getstr(num)
from 表1
group by num--查询
select * from #T