What is the equivalent of String.Join on TSQL? [du

问题:

Possible Duplicate:
Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

Hi all!

I'm looking for an easy way to concatenate 'n' values. Something like:

SELECT MyConcat(',', T.TextColumn)
FROM SomeTable AS T
WHERE T.SomeOtherColumn = SomeCondition

So if I have a table like:

SomeTable:
Id  | TextColumn | SomeOtherColumn
----+------------+----------------
1   | Qwerty     | Y
2   | qwerty     | N
3   | azerty     | N
4   | Azerty     | Y

It would result in something like:

SQL:
SELECT MyConcat(';', T.TextColumn)
FROM SomeTable AS T
WHERE T.SomeOtherColumn = 'Y'

RESULT:
'Qwerty;Azerty'

回答1:

This should do the trick:

DECLARE @Result VARCHAR(MAX);

SELECT
    @Result = CASE
        WHEN @Result IS NULL
        THEN T.TextColumn
        ELSE @Result + ';' + T.TextColumn
    END
FROM
    SomeTable AS T
WHERE
    T.SomeOtherColumn = 'Y';

SELECT @Result

回答2:

SELECT CAST(TextColumn + ';' AS VARCHAR(MAX)) 
FROM SomeTable
WHERE SomeOtherColumn = 'Y'
FOR XML PATH ('')

If you don't like the trailing ; you can remove the character from the result.

---

EDIT IN 2017

Many platforms now support the windowing function LISTAGG()