将多个结果行连接到单个结果中而不使用FOR XML PATH

来自MVP Rob Farley :

SELECT SUBSTRING((
  SELECT ('; ' + RTRIM(c.SomeField))
  FROM a (NOLOCK)
  INNER JOIN b (NOLOCK) ON a.aid = b.aid
  INNER JOIN c (NOLOCK) ON b.cid = c.cid
  WHERE a.zid = z.zid
  FOR XML PATH(''), ROOT('xml'), TYPE
).value('/xml[1]','varchar(max)'), 3, 1000)

但是,如果我真的将 然后我就能把里面的东西提取出来, 它把数据以自己的方式返回给我 原始(正确)表格。

您可以将这些语句作为一个批处理运行,以获得所需的内容:

declare @s as varchar(max); 
select @s = isnull(@s + '; ', '') + SomeField
from (
    SELECT RTRIM(c.SomeField) as SomeField
    FROM a (NOLOCK) 
    INNER JOIN b (NOLOCK) ON a.aid = b.aid 
    INNER JOIN c (NOLOCK) ON b.cid = c.cid 
    WHERE a.zid = z.zid 
) a; 
select @s as SomeFieldList; 

declare @s as varchar(max);  
select @s = isnull(@s + '; ', '') + SomeField
from ( 
    SELECT 'aaa' as SomeField
    UNION ALL
    SELECT 'bbb' 
    UNION ALL
    SELECT 'ccc' 
) a;  
select @s as SomeFieldList;