SQLServer2005 数据项的分拆与合并

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 '' "/> '' , 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 '' <root><v> ''  + REPLACE(@SpliteColName, N '' @Quote '' , N '' </v><v> '' ) + N '' </v></root> '' )
    FROM @TableName
) a
'
     ELSE
         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
    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 ' <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           90
2           10
3           20
4           30
5           40
6           50
7           60

(7 行受影响)