我们使用一组Case/When语句将其作为一个表字段引用。当我们在报告中引用该字段时,我们希望返回值用逗号分隔。现在我们可以只硬编码逗号,但是如果只有一个结果或者没有剩余的结果,我们就会有一个额外的逗号,这看起来很奇怪。
当前示例结果:"Camera Not WorkingCamera Needs Dirty/Needs Camera“
期望结果:“相机不工作,相机需要重新对焦,相机脏/需要清洗,相机重新定位”
(CASE WHEN pgs.[Camera Not Working] = 'True' THEN 'Camera Not Working' ELSE '' END +
CASE WHEN pgs.[Camera Needs Refocusing] = 'True' THEN 'Camera Needs Refocusing' ELSE '' END +
CASE WHEN pgs.[Camera Dirty/Needs Cleaning] = 'True' THEN 'Camera Dirty/Needs Cleaning' ELSE '' END +
CASE WHEN pgs.[Reposition Camera] = 'True' THEN 'Reposition Camera' ELSE '' END) as cameraissues,
(CASE WHEN pgs.[Network Down] = 'True' THEN 'Network Down' ELSE '' END +
CASE WHEN pgs.[Issue with Security Alarm Panel] = 'True' THEN 'Issue with Security Alarm Panel' ELSE '' END +
CASE WHEN pgs.[System will not open on computer] = 'True' THEN 'System will not open on computer' ELSE '' END +
CASE WHEN pgs.[Cameras not showing on TVs] = 'True' THEN 'Cameras not showing on TVs' ELSE '' END) as securityissues'发布于 2021-03-29 23:19:27
不是很漂亮,但你总是可以在开头添加,所以你总是在开头多加一个逗号,然后在准备好字符串后删除它:
STUFF((
CASE WHEN pgs.[Camera Not Working] = 'True' THEN ',Camera Not Working' ELSE '' END + CASE WHEN pgs.[Camera Needs Refocusing] = 'True' THEN ',Camera Needs Refocusing' ELSE '' END + CASE WHEN pgs.[Camera Dirty/Needs Cleaning] = 'True' THEN ',Camera Dirty/Needs Cleaning' ELSE '' END + CASE WHEN pgs.[Reposition Camera] = 'True' THEN ',Reposition Camera' ELSE '' END
), 1,1,'') as cameraissues,
STUFF((
CASE WHEN pgs.[Network Down] = 'True' THEN ',Network Down' ELSE '' END + CASE WHEN pgs.[Issue with Security Alarm Panel] = 'True' THEN ',Issue with Security Alarm Panel' ELSE '' END + CASE WHEN pgs.[System will not open on computer] = 'True' THEN ',System will not open on computer' ELSE '' END + CASE WHEN pgs.[Cameras not showing on TVs] = 'True' THEN ',Cameras not showing on TVs' ELSE '' END
), 1,1,'') as securityissues 因此,我为每个,语句添加了一个逗号,然后使用stuff函数将字符串中的第一个字符替换为一个空字符的逗号case
发布于 2021-03-29 23:54:33
您可以添加一个额外的最后一个字符,然后对其执行一个replace。在使用case表达式构建字符串后,只需添加一个额外的字符,例如另一个逗号,这样字符串的末尾就会有',,‘。
准备就绪后,您可以使用replace((entire case epxression block + ','),',,','')包装整个代码块
https://stackoverflow.com/questions/66856677
复制相似问题