如何把,1,2,3,这样id存放格式通过视图查询对应的名称来？

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