SQL Server2005数据项的分拆与合并:
参考示例如下：
-- =============================================
-- 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'''', N'''')
, N'', N''"/>'', 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)
,@KeyColName nvarchar(20)
,@SpliteColName 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
KeyCol, NewValue
FROM
(
SELECT
KeyCol = @KeyColName
,SpliteCol = CONVERT(xml, N'''' + REPLACE(@SpliteColName, N''@Quote'', N'''') + N'''')
FROM @TableName
) a
'
ELSE
SET @SQL = N'
SELECT
KeyCol, NewValue
FROM
(
SELECT
KeyCol = @KeyColName
,SpliteCol = CONVERT(xml, N'''' + REPLACE(@SpliteColName, N''@Quote'', N'''') + N'''')
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'@TableName', @TableName)
SET @SQL = REPLACE(@SQL, N'@KeyColName', @KeyColName)
SET @SQL = REPLACE(@SQL, N'@SpliteColName', @SpliteColName)
SET @SQL = REPLACE(@SQL, N'@Quote', @Quote)
IF NOT @Where IS NULL
SET @SQL = REPLACE(@SQL, N'@Where', @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]'
,@Quote = ','
--删除临时表
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'' + REPLACE(
@Input
,@Quote
,N'') + N'')
) 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 90
2 10
3 20
4 30
5 40
6 50
7 60
(7 行受影响)
