22,300
社区成员




if object_id('test.dbo.recordet') is not null drop table recordet
create table recordet(reId int identity(1,1),code varchar(20),rName varchar(100))
insert into recordet
select '01','第一' union all
select '01','第二' union all
select '01','第三' union all
select '02','第四' union all
select '01','第五' union all
select '02','第六' union all
select '04','第七'
select * from recordet
--
reId code rName
----------- -------------------- ------------------------------------
1 01 第一
2 01 第二
3 01 第三
4 02 第四
5 01 第五
6 02 第六
7 04 第七
if object_id('test.dbo.fstr') is not null drop function fstr
go
create function fstr(@code varchar(20)) returns varchar(1000)
as
begin
declare @fstr varchar(1000)
select @fstr=isnull(@fstr+',','')+rtrim(rName) from recordet where code=@code
return @fstr
end
go
select distinct code,rName=dbo.fstr(code) from recordet group by code
code rName
-------------------- ------------------------------------------------
01 第一,第二,第三,第五
02 第四,第六
04 第七
--sql2005方法:
--SQL2005用XML:
select distinct code,rName=stuff((select ','+rtrim(rName) from recordet
where code=t.code for xml path('')),1,1,'')
from recordet t
declare @str nvarchar(20)
set @str=',1,2,3'
select replace(@str,',',N',类型名称') A
A
-------------------------------
,类型名称1,类型名称2,类型名称3
(1 row(s) affected)
create function [dbo].[fn_split](@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
declare @s varchar(20)
set @s =',1,2,3,';
select * from dbo.fn_split(substring(@s,2,len(@s)-2),',')
--结果
1
2
3