关于SQL SERVER 数据库中列转行的研究

关于SQL SERVER 数据库中列转行的研究
今天程序开发中遇到这样的一个问题：就是把一列带有逗号分隔的字符串数据转换成行数据显示。
解决方法中发现了两种方法，可两种方法的性能却是大大不一样

图1
图一有两行都包含逗号分隔的字符串
可要求的解决结果如图二所示：

图二
图一中的原始数据有41万条之多，图二显示列转行之后的数据却又近147万条之多，
图二的关键函数是OUTER APPLY，而且性能很高，用时大概只有8秒
参考代码如下：
SELECT A.ID,A.wfxwType,A.JCRQ,A.zgdwid,B.wfxwValue
FROM(
SELECT StrXml = CONVERT(XML, ‘‘+REPLACE(t1.wfxwValue, ‘,’, ‘‘)+’‘),t1.ID,t1.wfxwType,t1.JCRQ,t1.zgdwid
FROM (
SELECT ID,’wfxw’ AS wfxwType,wfxw AS wfxwValue,JCRQ,zgdwid
FROM dbo.XFJD_FLWSJ_ZLGZTZS
WHERE wfxw is not NULL and wfxw <> ” and wfxw <> ‘/’
UNION
SELECT ID,’xqwfxw’ AS wfxwType,xqwfxw AS wfxwValue,JCRQ ,zgdwid
FROM dbo.XFJD_FLWSJ_ZLGZTZS
WHERE xqwfxw is not NULL and xqwfxw <> ” and xqwfxw <> ‘/’) t1
)A
OUTER APPLY
(
SELECT wfxwValue = N.v.value(‘.’, ‘nvarchar(40)’)
FROM A.StrXml.nodes(‘/root/v’) N(v)
)B

上面是一种解决方式，还有一种解决方式：如图三：

图三
图三所示方法也可以达到目的，关键函数是substring，但是其性能却大大下降，用时200秒，而且这个方法还有一个限制，就是图一所示的wfxw和xqwfxw两个字段的长度不能大于2047字节，这个是由 master..spt_values的原因，
参考代码如下：

SELECT t1.ID,t1.wfxwType,
SUBSTRING(t1.wfxwValue,number,CHARINDEX(‘,’,t1.wfxwValue+’,’,number)-number) as wfxwValue,
t1.JCRQ,t1.zgdwid
FROM
(
SELECT ID,’wfxw’ AS wfxwType,wfxw AS wfxwValue,JCRQ,zgdwid
FROM
dbo.XFJD_FLWSJ_ZLGZTZS
WHERE wfxw is not NULL and wfxw <> ” and wfxw <> ‘/’
UNION
SELECT ID,’xqwfxw’ AS wfxwType,xqwfxw AS wfxwValue,JCRQ ,zgdwid
FROM dbo.XFJD_FLWSJ_ZLGZTZS
WHERE xqwfxw is not NULL and xqwfxw <> ” and xqwfxw <> ‘/’
) t1,master..spt_values s
WHERE
s.number >=1 and s.number<=len(t1.wfxwValue)
and s.type=’p’
and substring(‘,’+t1.wfxwValue,s.number,1)=’,’

大家看到图二所示方法的结果和图三所示的结果是相同的，都是1469998条记录，都能达到目的，但是图二效率高的多，所以推荐图二所示的方法