What is the equivalent of String.Join on TSQL?

Question

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'

I've found a solution by creating the following function: CREATE FUNCTION [dbo].[JoinTexts] ( @delimiter VARCHAR(20) , @whereClause VARCHAR(1) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Texts VARCHAR(MAX) SELECT @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto FROM SomeTable AS T WHERE T.SomeOtherColumn = @whereClause RETURN @Texts END GO Usage: SELECT dbo.JoinTexts(' , ', 'Y') — Gil, May 30 '11 at 15:14

score 46 · Accepted Answer · answered May 29 '11 at 22:35

46

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

answered May 29 '11 at 22:35

domager

758
5
13

2

Caution: This will produce unexpected results if any of the values in `textcolumn` are null. To see what I mean, put this under the DECLARE line and run it: `with sometable as (select 'a' as textcolumn, 'y' as someothercolumn union all select 'b' ,'n' union all select null, 'y' union all select 'd', 'y' ) ` -> expecting 'ad', but actual result is 'd', because `null + 'some text'` is `null`. Consider using CONCAT instead, or COALESCE – Caius Jard Apr 26 '18 at 14:56

Hogan · Answer 2 · 2017-07-14T20:18:31.293

34

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()

edited Jul 14 '17 at 20:18

answered May 29 '11 at 21:33

Hogan

69,564
10
76
117

My results were truncated to 2034 results. I had better luck with https://stackoverflow.com/questions/5655332/using-sql-server-for-xml-convert-result-datatype-to-text-varchar-string-whate/5658758#5658758 – Jay Sullivan Apr 27 '18 at 19:33
2

For SQL Server you can now use `STRING_AGG`. [STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) – aboy021 Oct 06 '21 at 19:24
For Oracle and DB2 you can use `LISTAGG()` @aboy021 -- This answer was from over 10 years ago! – Hogan Oct 06 '21 at 19:42

What is the equivalent of String.Join on TSQL?

2 Answers2

Linked