关于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条记录,都能达到目的,但是图二效率高的多,所以推荐图二所示的方法