SQL2000一列转一行的存储过程

IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('LineToRow') AND TYPE='P')
DROP PROC LineToRow
GO
--20100526 表中某一字段的记录转为一行字符串输出
--@fields 表示表中的一个字段，@RecordSQL 查询语句
Create  proc  dbo.LineToRow (@fields nvarchar(10), @RecordSQL nvarchar(500)) 
as
declare @SQL2 varchar(8000)
SET @SQL2='declare @sql varchar(8000)' + CHAR(13)
SET @SQL2=@SQL2+' IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[temp]'') ) '
SET @SQL2=@SQL2+'drop table temp'
SET @SQL2=@SQL2+' set @sql = ''select ''+ char(39)'+ CHAR(13)
SET @SQL2=@SQL2+ ' select @sql=@sql + '+ @fields +' + '','' from ('+ @RecordSQL +') as a '+ CHAR(13)
SET @SQL2=@SQL2+ 'set @sql = left(@sql,len(@sql)-1)+ char(39) + ''as A1 into temp '''+ CHAR(13)
SET @SQL2=@SQL2+ 'exec(@sql)'
exec(@sql2)

 

 

 

结果
A1
本市,国内,国外