drop table #temp
create table #temp (id int)
declare @a int
declare @b varchar(50)
set @a=1
while(@a<6)
begin
set @b='[000'+cast(@a as varchar(10))+']'
exec('alter table #temp add ' + @b+ ' varchar(50) ')
set @a=@a+1
end
select * from #temp
create function dbo.fun_split
(
@str varchar(4000),
@split varchar(2)
)
returns
@table Table(col varchar(50))
as
begin
while(charindex(@split,@str) <> 0)
begin
insert into @table values(substring(@str,1,charindex(@split,@str) - 1))
set @str = stuff(@str,1,charindex(@split,@str),'')
end
insert into @table values(@str)
end
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END