T-SQL.如何在一个 SELECT 中将列表创建为逗号分隔的字符串? [英] T-SQL. How create list as comma-separated string in one SELECT?

问题描述

MS SQL 2005.T-SQL.

MS SQL 2005. T-SQL.

我发现很多很好的解决方案如何拆分字符串.但是如何将内部 SELECT 的结果组合为字符串(例如，使用逗号分隔符)?代码:

I find many good solutions how split string. But how combine result from inner SELECT as string (for example, with comma-separator)? Code:

SELECT b.date,
(SELECT o.number FROM order o WHERE o.number = m.number ) AS orderList

FROM bank b, movemoney m

WHERE b.code = m.code

<小时>

表 'movemoney' 由表 'order' 中的零个或几个订单组成.如何在一个 SELECT 语句中将orderList"创建为字符串?

推荐答案

如果需要，您还可以将 XML PATH 放入 SELECT 的子查询中.
我只是更喜欢这种结构:

You can also put the XML PATH into a subquery in the SELECT if you want.
I just prefer this construct:

SELECT
    b.date,
    SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS orderList
FROM
    bank b
    JOIN 
    movemoney m ON b.code = m.code
    OUTER APPLY
    (
    SELECT
        ',' + concatenatedid
    FROM
        order o
    WHERE
         o.number = m.number 
    FOR XML PATH ('')
    ) foo(bar)

这篇关于T-SQL.如何在一个 SELECT 中将列表创建为逗号分隔的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！