SQL Server 列转行

1,2,3,4,5

以上是一个字符串或则一逗号分隔的数字。

这里希望用一条语句查询出这样的效果：

1
2
3
4
5

不知道怎样才能实现
[解决办法]
create function [dbo].[f_Split]
(
@SourceSql nvarchar(max),--源分隔字符串
@StrSeprate varchar(10)--分隔符
)
returns @temp table(a nvarchar (max))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
go
select * from f_Split('1,2,3,4,5',',')
[解决办法]
declare @str varchar(20)
set @str='1,2,3,4,5'
set @str=@str+','
while charindex(',',@str)>0
begin
select substring(@str,1,charindex(',',@str)-1)
set @str=substring(@str,charindex(',',@str)+1,len(@str))
end
[解决办法]

CREATE TABLE T1(F1 VARCHAR(20))
INSERT INTO T1 VALUES('1,2,3,4,5')

SELECT x.T.value('.','varchar(10)') AS ID
FROM (SELECT CONVERT(XML,'<T>' + REPLACE(F1,',','</T><T>')+'</T>') AS K FROM T1) A
CROSS APPLY K.nodes('T') x(T)

--------
ID
----------
1
2
3
4
5

(5 行受影响)

[解决办法]

--cardbase
if object_id('cardbase','u') is not null
drop table cardbase

create table cardbase
(
[str] nvarchar(20)
)
go

--SQL
declare @length int
declare @start int
declare @str nvarchar(20)
set @str='1,2,3,4,5'
set @length=LEN(@str)
set @start=1

while(@start<=@length)
begin

    insert into cardbase select SUBSTRING(@str,@start,1)
set @start=@start+2
end
select *From cardbase

--结果集
/*
1
2
3
4
5
*/