Concat case语句

Question

我对编程非常陌生，但我正在尝试如何(或者如果我可以)连接下面的代码，理想情况下不包括任何用逗号分隔的空返回。

举个例子：-

德国、拉脱维亚、挪威

英国、法国、拉脱维亚

任何帮助和解释都将不胜感激。

Select 
*,

CASE 
WHEN PT_ID1 != 0 THEN 'Germany' 
WHEN PT_ID2 != 0 THEN 'Great Britain'
WHEN PT_ID3 != 0 THEN 'France'
WHEN PT_ID4 != 0 THEN 'Latvia'
WHEN PT_ID5 != 0 THEN 'Norway'
END AS Country

FROM      CTE2 C1
FULL JOIN CTE  C ON  C.PT_ID1 = C1.PT_ID2
FULL JOIN CTE3 D ON  D.PT_ID3 = C1.PT_ID2
FULL JOIN CTE4 E ON  E.PT_ID4 = C1.PT_ID2
FULL JOIN CTE5 F ON  F.PT_ID5 = C1.PT_ID2

order by MRN asc

Answer 1

对每个国家/地区使用不同的CASE语句

Select 
*,

 STUFF(CASE WHEN PT_ID1 != 0 THEN ', Germany' ELSE '' END
+ CASE WHEN PT_ID2 != 0 THEN ', Great Britain' ELSE '' END
+ CASE WHEN PT_ID3 != 0 THEN ', France' ELSE '' END
+ CASE WHEN PT_ID4 != 0 THEN ', Latvia' ELSE '' END
+ CASE WHEN PT_ID5 != 0 THEN ', Norway' ELSE '' END, 1, 2, '') AS Country

FROM      CTE2 C1
FULL JOIN CTE  C ON  C.PT_ID1 = C1.PT_ID2
FULL JOIN CTE3 D ON  D.PT_ID3 = C1.PT_ID2
FULL JOIN CTE4 E ON  E.PT_ID4 = C1.PT_ID2
FULL JOIN CTE5 F ON  F.PT_ID5 = C1.PT_ID2