sql 拼接字符串单条拆分多条

   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

上一篇:【题解】合唱队形——LIS坑爹的二分优化


下一篇:Java(面试题):字符串截取