/*
如讲以某个字符相隔的字符串转换为表
如字符串 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
SQLServer中将字符串的列转行和行转列
于 2022-11-21 15:23:12 首次发布