SQLServer2005 数据项的分拆与合并

发布时间：2020-12-12 16:00:13 所属栏目：MsSql教程来源：网络整理

导读：Author: 水如烟这是在邹建代码的基础上尝试的。 -- ?============================================= -- ?Author:?LzmTW -- ?Create?date:?20080102 -- ?Description:????连接子字符串 -- ?@TableName:?数据所在的表的名称 -- ?@KeyColName:?连接子字符串所

Author:水如烟

这是在邹建代码的基础上尝试的。

-- ?=============================================
-- ?Author:?LzmTW
-- ?Create?date:?20080102
-- ?Description:????连接子字符串
-- ?@TableName:?数据所在的表的名称
-- ?@KeyColName:?连接子字符串所依据的键值所在的列
-- ?@JoinColName:?包含要连接的子字符串所在的列
-- ?@Quote:?分隔子字符串
-- ?@Where:?选择条件，不包含Where
-- ?=============================================
CREATE ? PROCEDURE ? [ Helper ] . [ JoinValue ]
????? @TableName ? nvarchar ( 100 )
????, @KeyColName ? nvarchar ( 20 )
????, @JoinColName ? nvarchar ( 20 )
????, @Quote ? nvarchar ( 10 )? = ?N ' , '
????, @Where ? nvarchar ( max )? = ? NULL
AS
BEGIN
???? SET ?NOCOUNT? ON ;

???? DECLARE ?
????????? @SQL ? nvarchar ( max )
???? IF ? @Where ? IS ? NULL
???????? SET ? @SQL ? = ?N '
SELECT?*?
FROM
(
????SELECT?DISTINCT?KeyCol?=?@KeyColName?
????FROM?@TableName
)a
'
???? ELSE
???????? SET ? @SQL ? = ?N '
SELECT?*?
FROM
(
????SELECT?DISTINCT?KeyCol?=?@KeyColName?
????FROM?@TableName
????WHERE?@Where
)a
'
???? SET ? @SQL ? = ? @SQL ? + ?N '
OUTER?APPLY?(
????SELECT?NewValues?=
????STUFF(
????REPLACE(
????REPLACE(
????REPLACE(?
????????????????(
????????????????????SELECT?JoinCol?=?@JoinColName?
????????????????????FROM?@TableName?b
????????????????????WHERE?@KeyColName?=?a.KeyCol
????????????????????FOR?XML?RAW
????????????????)
????,?N '' <row/> '' ,?N '''' )
????,?N '' <row?JoinCol=" '' ,?N '' @Quote '' )
????,?N '' "/> '' ,?1,?LEN(N '' @Quote '' ),?N '''' )
)?c '

???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @TableName ' ,? @TableName )
???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @KeyColName ' ,? @KeyColName )
???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @JoinColName ' ,? @JoinColName )
???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @Quote ' ,? @Quote )

???? IF ? NOT ? @Where ? IS ? NULL
???????? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @Where ' ,? @Where )

--???? PRINT ? @SQL
???? EXEC ?sp_executesql? @SQL
END

GO

-- ?=============================================
-- ?Author:????LzmTW
-- ?Create?date:?20080102
-- ?Description:????分拆字符串
-- ?@TableName:?数据所在的表的名称
-- ?@KeyColName:?分拆为子字符串所依据的键值所在的列
-- ?@SpliteColName:?包含要分拆的字符串所在的列
-- ?@Quote:?分隔子字符串
-- ?@Where:?选择条件，不包含Where
-- ?=============================================
CREATE ? PROCEDURE ? [ Helper ] . [ SpliteValues ]
????? @TableName ? nvarchar ( 100 )
????, @SpliteColName ? nvarchar ( 20 )
????, @Where ? nvarchar ( max )? = ? NULL
AS
BEGIN
???? SET ?NOCOUNT? ON ;

???? DECLARE ?
????????? @SQL ? nvarchar ( max )

???? IF ? @Where ? IS ? NULL
???????? SET ? @SQL ? = ?N '
SELECT
????KeyCol,?NewValue
FROM
(
????SELECT?
?????????KeyCol?=?@KeyColName
????????,SpliteCol?=?CONVERT(xml,?N '' <root><v> '' ?+?REPLACE(@SpliteColName,?N '' @Quote '' ,?N '' </v><v> '' )?+?N '' </v></root> '' )
????FROM?@TableName
)?a
'
???? ELSE
???????? SET ? @SQL ? = ?N '
SELECT
????KeyCol,?N '' </v><v> '' )?+?N '' </v></root> '' )
????FROM?@TableName
????WHERE?@Where
)?a
'
???? SET ? @SQL ? = ? @SQL ? + ?N '
OUTER?APPLY?
????(
????????SELECT?NewValue?=??N.v.value(N '' . '' ,? '' nvarchar(max) '' )
????????FROM?SpliteCol.nodes(N '' /root/v '' )?N(v)
????)?b '

???? SET ? @SQL ? = ? REPLACE ( @SQL ,?N ' @SpliteColName ' ,? @SpliteColName )
???? SET ? @SQL ? = ? REPLACE ( @SQL ,? @Where )

???? EXEC ?sp_executesql? @Sql
END

示例

SET ?NOCOUNT? ON
CREATE ? TABLE ?## Table ( [ keyCol ] ? varchar ( 3 ),? [ NewValues ] ? varchar ( max ))

-- 原数据
SELECT ?
????? [ title_id ]
????, [ title ]
FROM ? [ pubs ] . [ dbo ] . [ titles ]
WHERE ? [ type ] ? LIKE ? ' p% '

-- 以title_id的前两个字符为参考键值，合并title到一个临时表中
INSERT ? INTO ?## Table
EXECUTE ? [ ChineseHoliday ] . [ Helper ] . [ JoinValue ] ?
????????? @TableName ? = ? ' [pubs].[dbo].[titles] '
????????, @KeyColName ? = ? ' LEFT([title_id],?2) '
????????, @JoinColName ? = ? ''' 《 '' +[title]?+? '' 》 '''
????????, @Quote ? = ? ' , '
????????, @Where ? = ? ' [type]?LIKE? '' p% '''

-- 显示
SELECT ? * ? FROM ?## Table

-- 对临时表NewValues的值进行分拆
EXECUTE ? [ ChineseHoliday ] . [ Helper ] . [ SpliteValues ] ?
????????? @TableName ? = ? ' ##Table '
????????, @KeyColName ? = ? ' [keyCol] '
????????, @SpliteColName ? = ? ' [NewValues] '
????????, '

-- 删除临时表
DROP ? TABLE ?## Table

结果：

title_id?title
--------?--------------------------------------------------------------------------------
PC1035???But?Is?It?User?Friendly?
PC8888???Secrets?of?Silicon?Valley
PC9999???Net?Etiquette
PS1372???Computer?Phobic?AND?Non-Phobic?Individuals:?Behavior?Variations
PS2091???Is?Anger?the?Enemy?
PS2106???Life?Without?Fear
PS3333???Prolonged?Data?Deprivation:?Four?Case?Studies
PS7777???Emotional?Security:?A?New?Algorithm

keyCol?NewValues
------?------------------------------------------------------------------------------------------------------------------------------------
PC?????《But?Is?It?User?Friendly?》,《Secrets?of?Silicon?Valley》,《Net?Etiquette》??????
PS?????《Computer?Phobic?AND?Non-Phobic?Individuals:?Behavior?Variations》,《Is?Anger?the?Enemy?》,《Life?Without?Fear》,《Prolonged?Data?Deprivation:?Four?Case?Studies》,《Emotional?Security:?A?New?Algorithm》??????????

KeyCol?NewValue
------?------------------------------------------------------------------------------------------------------------------------------------
PC?????《But?Is?It?User?Friendly?》
PC?????《Secrets?of?Silicon?Valley》
PC?????《Net?Etiquette》
PS?????《Computer?Phobic?AND?Non-Phobic?Individuals:?Behavior?Variations》
PS?????《Is?Anger?the?Enemy?》
PS?????《Life?Without?Fear》
PS?????《Prolonged?Data?Deprivation:?Four?Case?Studies》
PS?????《Emotional?Security:?A?New?Algorithm》

继续:字符串的分拆

-- ?=============================================
-- ?Author:????LzmTW
-- ?Create?date:?20080108
-- ?Description:????拆分字符串
-- ?=============================================
CREATE ? FUNCTION ? [ Func ] . [ Splite ] ?
(
????? @Input ? nvarchar ( max )?
????, @Quote ? nvarchar ( max )
)
RETURNS ?
@Table ? TABLE ?
(
????? [ ID ] ? int ? identity ( 1 , 1 )? PRIMARY ? KEY
????, [ Value ] ? nvarchar ( max )
)
AS
BEGIN
???? INSERT ? @Table
???? SELECT
???????? [ Value ] ? = ?NewValue
???? FROM
????(
???????? SELECT ?
????????????SpliteCol? = ? CONVERT (
?????????????????xml
????????????????,N ' <root><v> ' ? + ? REPLACE (
????????????????????? @Input
????????????????????, @Quote
????????????????????,N ' </v><v> ' )? + ?N ' </v></root> ' )
????)?a
???? OUTER ?APPLY?
????????(
???????????? SELECT ?NewValue? = ??N.v.value(N ' . ' ,? ' nvarchar(max) ' )
???????????? FROM ?SpliteCol.nodes(N ' /root/v ' )?N(v)
????????)?b

???? RETURN ?
END

示例:

定义新行,

CREATE ? FUNCTION ? [ Const ] . [ NewLine ]
(
)
RETURNS ? nchar ( 2 )
AS
BEGIN

???? DECLARE ? @Result ? nchar ( 2 )

???? SELECT ? @Result ? = ? char ( 13 )? + ? char ( 10 )

???? RETURN ? @Result
END

DECLARE
????? @Input ? nvarchar ( max )?
????, @Quote ? nvarchar ( max )

SET ? @Input ? = ?N ' 90
10
20
30
40
50
60 '

SET ? @Quote ? = ? [ Const ] .NewLine()

SELECT ? * ? FROM ? [ Func ] . [ Splite ] ?( @Input ,? @Quote )

结果

ID??????????Value-----------?------1???????????902???????????103???????????204???????????305???????????406???????????507???????????60(7?行受影响)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!