server多列转行 sql_sql server 2005一列转成多行

给个以前写的例子你看看，正好就是解决你的需求的。

如下代码在sqlserver 2005 上运行测试通过

create table tbl2

(

c1 varchar(100),

c2 varchar(100)

)

insert into tbl2

values('01','g,x,z');

insert into tbl2

values('02','m,f,k');

create function fn_split(@strings nvarchar(4000),@splitChar nvarchar(100))

returns @splitTbl table (col nvarchar(1000))

as

begin

declare @CI int;

declare @splitItem nvarchar(1000);

set @CI=charIndex(@splitChar,@strings);

while @CI>0

begin

set @splitItem=substring(@strings, 1, @CI-1);

set @strings=substring(@strings, @CI+len(@splitChar),len(@strings)-@CI);

set @CI=charIndex(@splitChar,@strings);

insert @splitTbl select @splitItem;

end

insert @splitTbl select @strings;

return;

end

create function fn_GetSplitList()

returns @splitTable table(c1 varchar(100), c2 varchar(100))

as

begin

declare @c1 varchar(100),@c2 varchar(100);

declare c_cursor1 cursor

for select c1,c2 from tbl2;

open c_cursor1;

fetch next from c_cursor1 into @c1,@c2;

while @@fetch_status=0

begin

insert into @splitTable

select @c1, col from dbo.fn_split(@c2,',');

fetch next from c_cursor1 into @c1,@c2;

end

close c_cursor1;

deallocate c_cursor1;

return;

end

select * from dbo.fn_GetSplitList();