拆分字符串为单条记录

原来的数据如上图上图，需要得到的结果如下图

sql语句如下：

SELECT  vo.name_id ,
          COMNAME = SUBSTRING(vo.COMNAME, number,
                              CHARINDEX(',', vo.COMNAME + ',', number)
                              - b.number)
  FROM    dbo.verna_org AS vo
          JOIN master..spt_values b ON b.type = 'p'
                                       AND CHARINDEX(',', ',' + vo.COMNAME,
                                                     number) = number