create function getobj(@name nvarchar(10))
returns nvarchar(1000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+選修課 from tablea where 姓名=@name
set @s=stuff(@s,1,1,'')
return(@s)
end
goselect 姓名,dbo.getobj(姓名) from tablea group by 姓名select * into tableb from (select 姓名,dbo.getobj(姓名) as 選修課 from tablea group by 姓名)t
returns nvarchar(1000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+選修課 from tablea where 姓名=@name
set @s=stuff(@s,1,1,'')
return(@s)
end
goselect 姓名,dbo.getobj(姓名) from tablea group by 姓名select * into tableb from (select 姓名,dbo.getobj(姓名) as 選修課 from tablea group by 姓名)t
create function cc(@name nvarchar(10))
returns nvarchar(1000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+選修課 from tablea where 姓名=@name
set @s=substring(@s,2,len(@s))
return(@s)
end
-----------end function --------------
select select 姓名,dbo.cc(姓名) into tableb from tablea group by 姓名
----由tablea得到tableb
select * from tableb ----顯示tableB