返回多个值的MS SQL Server子查询（字符串）

Question

我遇到问题，它将给出字符串结果的子查询结果连接起来。

其实我的子查询有一个CASE WHEN语句和列表函数在MS SQL服务器中不起作用。所以我需要以某种方式连接这个子查询的结果，以便结果在一行中可见。

这里是我的代码如下所示：

select 
.......,(select CASE WHEN pm.PaymentType = 1 THEN 'Cash'  
        WHEN pm.PaymentType = 2 THEN 'Check'  
        WHEN pm.PaymentType = 3 THEN 'Credit Card' 
        ELSE 'Money Order' 
       END 
      from 
      <some tables with all the joins> 
      where 
      <all the conditions>) AS [PAYMENT TYPE], 
....... 
from 
<some more tables with joins> 
where 
<some other conditions> 

感谢您的帮助！

Answer 1

一个所述的方法，我知道（也可能是最简单的你）是XML的绝招：

select 
......., 
    stuff(
      (
      select 
       ',' + 
       CASE 
        WHEN pm.PaymentType = 1 THEN 'Cash'  
        WHEN pm.PaymentType = 2 THEN 'Check'  
        WHEN pm.PaymentType = 3 THEN 'Credit Card' 
        ELSE 'Money Order' 
       END 
      from <some tables with all the joins> 
      where <all the conditions> 
      for xml path(''), type 
     ).value('.', 'varchar(max)') 
    ,1,1,'') AS [PAYMENT TYPE] 
....... 
from 
<some more tables with joins> 
where 
<some other conditions>