T-SQL. How create list as comma-separated string i

问题:

MS SQL 2005. T-SQL.

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

---

The table 'movemoney' consist of zero or few orders from the table 'order'. How create 'orderList' as string in one SELECT statement?

回答1:

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)