SELECT * FROM ( SELECT A.WS_ID , B.NEXT_OPERATOR FROM ( SELECT WS_ID , [NEXT_OPERATOR] = CONVERT(XML, '<root><v>' + REPLACE([NEXT_OPERATOR], ',', '</v><v>') + '</v></root>') FROM WS_INST_STEP MYSTEP WHERE MYSTEP.NEXT_OPERATOR IS NOT NULL GROUP BY MYSTEP.WS_ID , MYSTEP.NEXT_OPERATOR ) A OUTER APPLY ( SELECT NEXT_OPERATOR = N.v.value('.', 'varchar(100)') FROM A.[NEXT_OPERATOR].nodes('/root/v') N ( v ) ) B ) TT WHERE TT.NEXT_OPERATOR IN ( 870, 47, 51, 37, 60, 48, 46, 866, 54, 62, 63, 30, 56, 36, 867, 28, 58, 31, 43, 45, 32, 42, 65, 1892, 64, 40, 55, 34, 50, 53, 29, 57, 59, 44, 795, 41, 52, 35, 33, 869, 868, 1891, 38, 39, 49, 61 )
SELECT WS_ID , OPERATOR_AND_NEXT_OPERATOR = STUFF(( SELECT ',' + T_STEP_0.NEXT_OPERATOR FROM WS_INST_STEP AS T_STEP_0 WHERE T_STEP_0.WS_ID = T_STEP_1.WS_ID FOR XML PATH('') ), 1, 1, '') + ',' + STUFF(( SELECT ',' + T_STEP_0.OPERATOR FROM WS_INST_STEP AS T_STEP_0 WHERE T_STEP_0.WS_ID = T_STEP_1.WS_ID FOR XML PATH('') ), 1, 1, '') FROM WS_INST_STEP T_STEP_1 GROUP BY WS_ID
拼接字符串单条拆分多条
https://www.cnblogs.com/champaign/p/4159309.html