将字符串转成列

declare @sql varchar(300),@i int,@n int,@a int
set @a=1
set @sql='123|abc|xxx|ccc'
set @n=len(@sql)-len(replace(@sql,'|',''))
set @i=1
while @a<=@n
begin
select substring(@sql,@i,patindex('%|%',@sql)-1)
select @i=charindex('|',@sql,@i+1)+1
set @a=@a+1
end
//第二种

declare @s nvarchar(1000)
set @s='123|abc|xxx|ccc'
set @s='select '''+replace(@s,'|',''' as s union all select ''')+''''
print @s
exec(@s)
//
declare @s nvarchar(1000)
set @s='123|abc|xxx|ccc'
set @s='select * into ##t from( select '''+replace(@s,'|',''' as s union all select ''')+''''+')t select * from ##t '
print @s
exec(@s)