sqlServer 2008 一行转多行的问题

  前几天老大发来需求，是要出个报表来着。嗯，就是一行变多行。

　　来贴个简单的需求：

　　现有如下表格

　　

要求变成如下：

ok,因为有逗号，我首先想到的就是想办法把逗号去掉。结果发现sql没有提供像C#那样split的方法，所以就自己写了个如下：

/****** Object:  UserDefinedFunction [dbo].[mysplit]    Script Date: 09/19/2012 16:52:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[mysplit]--将以某分隔符分段的字串,按指定的顺序号提取子串 
(@str nvarchar(2000),--源字串 
@sn int, --提取序号 
@Deli varchar(1) --分隔符 
) 
RETURNS varchar(100) 
AS 
BEGIN 
declare @first int,@last int,@result varchar(1000),@sn0 int 
select @sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1) 
while @sn0!=@sn 
begin 
select @sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1 
end 
if @last-@first-1<0 
set @result='' 
else 
SET @RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1) 
RETURN ( @RESULT ) 
END 

GO

有了分开的方法，我就有了左连的想法：

if exists (select * from sysobjects where name='Cut')
drop proc Cut1
go
create proc Cut1
as
    SELECT id,dbo.mysplit(txt,1,',') AS txt,1 AS SN from PkTable 
    union 
    SELECT id,dbo.mysplit(txt,2,',') AS txt,1 AS SN from PKTable
    union 
    SELECT id,dbo.mysplit(txt,2,',') AS txt,1 AS SN from PKTablee 
    ORDER BY id,txt,sn 
go

exec  Cut1

结果是出来了，但是确是有不少空白字段的。大家可以试一下，这里就不再多说了

查查资料啊，各种问啊。终于寻到一个很神奇的办法哈

select 
a.id,b.txt
from 
(select id,txt=convert(xml,'<root><v>'+replace(txt,',','</v><v>')+'</v></root>') from PkTable)a
outer apply
(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b

OK 很成功！代码简洁 功能实现！

是的，哥们儿。我这里用到了sql xml

我用节点符号替换了逗号，然后用 C.V.value通过节点把他分离显示

来来，大家讨论下，第一次用，不是太熟，多多指教。

 

 

编辑上博友的好方法 供大家参考：

可以用：CTE
效率比 XML法快

with T (id,P1,P2) as
(
select id,charindex(',',','+name),charindex(',',name+',')+1from #T
unionall
select a.id,b.P2,charindex(',',name+',',b.P2)+1from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 -1) from #T a join T b on a.id=b.id orderby1