SQLServer中将字符串的列转行和行转列

/*

如讲以某个字符相隔的字符串转换为表
如字符串 Nothing ,is impossible,to,a,willing,heart
--列转行
 select * from udf_ConvertStrtoTable('Nothing ,is impossible,to,a,willing,heart',',')
 --行转列
 select * into #temp from udf_ConvertStrtoTable('Nothing ,is impossible,to,a,willing,heart',',')
 --将上面同样的查询结果写入临时表 #temp
 select stuff((select ','+A.Item from #temp A order bu Iden for xml path(''),1,1,'') As Result
 drop Table #temp --删除临时表
*/

CREATE FUNCTION [dbo].[udf_ConvertStrtoTable]
(@Str NVARCHAR(MAX),
@SplitSymbol CHAR(1)=',')
RETURNS @aTable Table
( 
Iden int,
Item varchar(500)
)
 as begin
  declare @i INT
   SET @i=0
   while RIGHT(@Str,1)=@SplitSymbol
   set @Str=LEFT(@Str,LEN(@Str)-1)
   declare @iIndex int
   set  @iIndex=CHARINDEX(@SplitSymbol,@Str)
   while @iIndex>0
	   begin
	   set @i=@i+1
	   Insert into @aTable
	   ( Iden ,Item )values(@i,LEFT(@Str,@iIndex-1))
	   select @Str=substring(@Str,@iIndex+1,6000)
	   set @iIndex=Charindex(@SplitSymbol,@Str)
	  end
	  if ltrim(rtrim(@Str))<>''
	  begin 
	  insert into @aTable  ( Iden ,Item ) values (@i+1,@Str)
	  end 
  return
  end