表结构如下
id value class
1 3,2,4,5 no
2 5,1,1+2 yes
将value列按逗号拆分后加一列code为排序，结果如下
id value class code
1 3 no 1
1 2 no 2
1 4 no 3
1 5 no 4
2 5 yes 1
2 1 yes 2
2 1+2 yes 3
------解决方案--------------------
create table TB_1 (ID INT ,VALUE VARCHAR(10),CLASS VARCHAR(10))
GO
INSERT INTO TB_1 VALUES (1,'3,2,4,5,','NO')
GO
INSERT INTO TB_1 VALUES (2,'5,1,1+2,','YES')
GO
SELECT * FROM TB_1
WITH TB_2 AS (
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE) STA,CHARINDEX(',',VALUE)-1 LENS FROM TB_1
UNION ALL
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE,STA+1) STA,CHARINDEX(',',VALUE,STA+1)-STA-1 LENS FROM TB_2 WHERE STA<>0)
--SELECT * FROM TB_2
SELECT ID,SUBSTRING(VALUE,STA-LENS,LENS),class FROM TB_2
WHERE STA<>0