SQL脚本整理系列一 分隔函数

原来效果：

fName    Scroe

王某某    101,102,109

李某某    102,103

王某某    103

李某某

李某某    101,102,103

王某某    222  

执行后效果：

name    score

李某某    101

李某某    102

李某某    103

王某某    101

王某某    102

王某某    103

王某某    109

王某某    222

--基础数据表创建

IF OBJECT_ID('Test1') >0

DROP TABLE dbo.Test1

CREATE TABLE Test1

    (

      fName NVARCHAR(10) ,

      Scroe VARCHAR(50)

    );

INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'王某某', -- fName - nvarchar(10)

          '101,102,109'  -- Scroe - varchar(50)

          )

INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'李某某', -- fName - nvarchar(10)

          '102,103'  -- Scroe - varchar(50)

          )

INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'王某某', -- fName - nvarchar(10)

          ''  -- Scroe - varchar(50)

          )

INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'李某某', -- fName - nvarchar(10)

          ''  -- Scroe - varchar(50)

          )

INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'李某某', -- fName - nvarchar(10)

          '101,102,103'  -- Scroe - varchar(50)

          )

 INSERT  dbo.Test1

        ( fName, Scroe )

VALUES  ( N'王某某', -- fName - nvarchar(10)

          ''  -- Scroe - varchar(50)

          )

 SELECT * FROM Test1

          --DECLARE @Scroe VARCHAR(max)

--创建分隔函数

 IF OBJECT_ID('getSplitResult') > 0

     DROP FUNCTION getSplitResult

 GO

 /*

 分隔函数

 */

 CREATE  FUNCTION dbo.getSplitResult

     (

       @SourceObject VARCHAR(MAX) ,--源对象

       @Split VARCHAR(1) = ','--分隔参数

     )

     --返回一个表

 RETURNS @tb TABLE ( score VARCHAR(10) )

 AS

     BEGIN

         --不含分隔符

         IF CHARINDEX(@Split, @SourceObject) = 0

             INSERT  @tb

                     ( score )

             VALUES  ( @SourceObject -- score - varchar(10)

                       );

         WHILE ( CHARINDEX(@Split, @SourceObject) > 0 )

             BEGIN

                 --插入数据

                 INSERT  @tb

                         ( score

                         )

                 VALUES  ( SUBSTRING(@SourceObject, 0,

                                     CHARINDEX(@Split, @SourceObject))  -- score - varchar(10)

                         );

                 SET @SourceObject = RIGHT(@SourceObject,

                                           LEN(@SourceObject)

                                           - CHARINDEX(@Split, @SourceObject))

                 IF ( CHARINDEX(@Split, @SourceObject) = 0

                      AND LEN(@SourceObject) <> 0

                    )

                     INSERT  @tb

                             ( score )

                     VALUES  ( @SourceObject -- score - varchar(10)

                               );

             END

         RETURN

     END
        

--创建存储过程

IF OBJECT_ID('sp_SplitResult') > 0

    DROP PROC sp_SplitResult

GO

CREATE  PROC sp_SplitResult

AS

    BEGIN

     --声明一个表

        SET   NOCOUNT ON

        DECLARE @tb TABLE

            (

              name VARCHAR(20) ,

              score VARCHAR(10)

            )

        DECLARE @name VARCHAR(20)= '' ,

            @SourceObject VARCHAR(MAX)

        --创建游标

        DECLARE cursor_tb CURSOR FAST_FORWARD

        FOR

            ( SELECT    fName ,

                        Scroe

              FROM      dbo.Test1

              WHERE     Scroe <> ''

            )

        OPEN  cursor_tb

        FETCH NEXT FROM cursor_tb

        INTO @name, @SourceObject;

        WHILE @@FETCH_STATUS = 0

            BEGIN

                INSERT  @tb

                        SELECT  @name ,

                                score

                        FROM    dbo.getSplitResult(@SourceObject, ',')

                FETCH NEXT FROM cursor_tb

        INTO @name, @SourceObject

            END

        CLOSE cursor_tb

        DEALLOCATE cursor_tb

        SELECT DISTINCT

                *

        FROM    @tb

    END

--  EXEC sp_SplitResult 

--执行结果

EXEC sp_SplitResult