SQL 将一个字段内用逗号分隔的内容分成多条记录

由于业务需求，我们可能会把一串以分割符字符串数据放到一个字段，如我们在客户端处理拆分是很简单的，不过这样做效果不太好，怎么用SQL SERVER 2008 来解决这件事件哪？

方案1

  用SQL SERVER XML 功能来解决

   
   
   

    
    
    1 
    
    
    --
    
    
    --> 生成测试数据: @T
    
    
    

    
    
    2 
    
    
     
    
    
    DECLARE
    
    
     
    
    
    @T
    
    
     
    
    
    TABLE
    
    
     (id 
    
    
    INT
    
    
    ,Col1 
    
    
    VARCHAR
    
    
    (
    
    
    50
    
    
    ),Col2 
    
    
    VARCHAR
    
    
    (
    
    
    50
    
    
    ))

    
    
    3 
    
    
     
    
    
    INSERT
    
    
     
    
    
    INTO
    
    
     
    
    
    @T
    
    
    

    
    
    4 
    
    
     
    
    
    SELECT
    
    
     
    
    
    1
    
    
    ,
    
    
    '
    
    
    卸货费
    
    
    '
    
    
    ,
    
    
    '
    
    
    运输车队业务, 客服接单业务
    
    
    '
    
    
     

    
    
    5 
    
    
     
    
    
    UNION
    
    
     
    
    
    ALL
    
    
    

    
    
    6 
    
    
     
    
    
    SELECT
    
    
     
    
    
    2
    
    
    ,
    
    
    '
    
    
    报关费
    
    
    '
    
    
    ,
    
    
    '
    
    
    报关业务、客服接单业务
    
    
    '
   
   
   

   
   
   

    
    
    --
    
    
    --SQL查询如下:
    
    
    

    
    
    

    
    
    SELECT
    
    
     
 T.id,
 A.x.value(
    
    
    '
    
    
    .
    
    
    '
    
    
    ,
    
    
    '
    
    
    varchar(10)
    
    
    '
    
    
    ) 
    
    
    AS
    
    
     code,
 B.x.value(
    
    
    '
    
    
    .
    
    
    '
    
    
    ,
    
    
    '
    
    
    varchar(10)
    
    
    '
    
    
    ) 
    
    
    AS
    
    
     code2

    
    
    FROM
    
    
     (

    
    
    SELECT
    
    
     
    
    
    *
    
    
    ,doc 
    
    
    =
    
    
     
    
    
    CONVERT
    
    
    (xml,
    
    
    '
    
    
    <v>
    
    
    '
    
    
    +
    
    
    REPLACE
    
    
    (code,
    
    
    '
    
    
    ,
    
    
    '
    
    
    ,
    
    
    '
    
    
    </v><v>
    
    
    '
    
    
    )
    
    
    +
    
    
    '
    
    
    </v>
    
    
    '
    
    
    ),
 doc2
    
    
    =
    
    
    CONVERT
    
    
    (xml,
    
    
    '
    
    
    <v>
    
    
    '
    
    
    +
    
    
    REPLACE
    
    
    (code2,
    
    
    '
    
    
    ,
    
    
    '
    
    
    ,
    
    
    '
    
    
    </v><v>
    
    
    '
    
    
    )
    
    
    +
    
    
    '
    
    
    </v>
    
    
    '
    
    
    )

    
    
    FROM
    
    
     
    
    
    @T
    
    
    
) 
    
    
    AS
    
    
     T

    
    
    CROSS
    
    
     APPLY doc.nodes(
    
    
    '
    
    
    //v
    
    
    '
    
    
    ) 
    
    
    AS
    
    
     A(x)

    
    
    CROSS
    
    
     APPLY doc2.nodes(
    
    
    '
    
    
    //v
    
    
    '
    
    
    ) 
    
    
    AS
    
    
     B(x)
   
   
   

方案2 

   用SQL SERVER WITH 递归查询

  
  
  

   
   
    1 
   
   
   WITH
   
   
    roy

   
   
    2 
   
   
    
   
   
   AS
   
   
    ( 
   
   
   SELECT
   
   
    Col1 ,

   
   
    3 
   
   
    COl2 
   
   
   =
   
   
    
   
   
   CAST
   
   
   (
   
   
   LEFT
   
   
   (Col2, 
   
   
   CHARINDEX
   
   
   (
   
   
   '
   
   
   ,
   
   
   '
   
   
   , Col2 
   
   
   +
   
   
    
   
   
   '
   
   
   ,
   
   
   '
   
   
   ) 
   
   
   -
   
   
    
   
   
   1
   
   
   ) 
   
   
   AS
   
   
    
   
   
   NVARCHAR
   
   
   (
   
   
   100
   
   
   )) ,

   
   
    4 
   
   
    Split 
   
   
   =
   
   
    
   
   
   CAST
   
   
   (
   
   
   STUFF
   
   
   (COl2 
   
   
   +
   
   
    
   
   
   '
   
   
   ,
   
   
   '
   
   
   , 
   
   
   1
   
   
   ,

   
   
    5 
   
   
    
   
   
   CHARINDEX
   
   
   (
   
   
   '
   
   
   ,
   
   
   '
   
   
   , Col2 
   
   
   +
   
   
    
   
   
   '
   
   
   ,
   
   
   '
   
   
   ), 
   
   
   ''
   
   
   ) 
   
   
   AS
   
   
    
   
   
   NVARCHAR
   
   
   (
   
   
   100
   
   
   ))

   
   
    6 
   
   
    
   
   
   FROM
   
   
    Tab

   
   
    7 
   
   
    
   
   
   UNION
   
   
    
   
   
   ALL
   
   
   

   
   
    8 
   
   
    
   
   
   SELECT
   
   
    Col1 ,

   
   
    9 
   
   
    COl2 
   
   
   =
   
   
    
   
   
   CAST
   
   
   (
   
   
   LEFT
   
   
   (Split, 
   
   
   CHARINDEX
   
   
   (
   
   
   '
   
   
   ,
   
   
   '
   
   
   , Split) 
   
   
   -
   
   
    
   
   
   1
   
   
   ) 
   
   
   AS
   
   
    
   
   
   NVARCHAR
   
   
   (
   
   
   100
   
   
   )) ,

   
   
   10 
   
   
    Split 
   
   
   =
   
   
    
   
   
   CAST
   
   
   (
   
   
   STUFF
   
   
   (Split, 
   
   
   1
   
   
   , 
   
   
   CHARINDEX
   
   
   (
   
   
   '
   
   
   ,
   
   
   '
   
   
   , Split), 
   
   
   ''
   
   
   ) 
   
   
   AS
   
   
    
   
   
   NVARCHAR
   
   
   (
   
   
   100
   
   
   ))

   
   
   11 
   
   
    
   
   
   FROM
   
   
    Roy

   
   
   12 
   
   
    
   
   
   WHERE
   
   
    split 
   
   
   >
   
   
    
   
   
   ''
   
   
   

   
   
   13 
   
   
    )

   
   
   14 
   
   
    
   
   
   SELECT
   
   
    COl1 ,

   
   
   15 
   
   
    COl2

   
   
   16 
   
   
    
   
   
   FROM
   
   
    roy

   
   
   17 
   
   
    
   
   
   ORDER
   
   
    
   
   
   BY
   
   
    COl1

   
   
   18 
   
   
   
   
   
   OPTION
   
   
    ( MAXRECURSION 
   
   
   0
   
   
    )