SQL拆分字符串构成新的记录

原数据表的数据格式如下

要求将字段vernacular中用逗号”,”分隔的字符串单独取出，并且把相应行的spnumber也取出，最后形成一条新的记录。

要求的结果如下

 

实现的SQL语句如下

 

--拆分字段字符串形成新的记录
CREATE   TABLE tb
    (
      spnumber INT,
      vernacular VARCHAR(1000)
    )

INSERT  INTO tb
        SELECT  SPNUMBER,
                COMNAME
        FROM    dbo.CommonName


DECLARE @i INT
SELECT  @i = MAX(LEN(vernacular))
FROM    tb
SET ROWCOUNT @i
SELECT  spnumber = IDENTITY( INT)
INTO    #t
FROM    syscolumns a,
        syscolumns b
SET ROWCOUNT 0
SELECT  a.spnumber,
        vernacular = SUBSTRING(a.vernacular, b.spnumber,
                               CHARINDEX(',', a.vernacular + ',', b.spnumber)
                               - b.spnumber)
FROM    tb a,
        #t b
WHERE   SUBSTRING(',' + a.vernacular, b.spnumber, 1) = ','
ORDER   BY a.spnumber
DROP TABLE   #t
  go