How to concatenate results in T-SQL into column?

Question

I'm working on a query which should give me result like:

|--Name--|--Surname--|--Language--|--Date--   | 
| James  |  Hetfield | en,gb,fr   | 2011-01-01|
| Lars   |  Ulrich   | gb,fr,ca   | 2011-01-01|

but my select gets set of rows like:

| James   |  Hetfield    | en   | 2011-01-01|
| James   |  Hetfield    | gb   | 2011-01-01|
| James   |  Hetfield    | fr   | 2011-01-01|
| Lars    |  Ulrich      | gb   | 2011-01-01|
| Lars    |  Ulrich      | fr   | 2011-01-01|
| Lars    |  Ulrich      | ca   | 2011-01-01|

Which best method you recommend to convert sets of result into coma-separated values in grouped column 'on the fly'? I've found that CROSS APPLY could do the work but people says that this method is very consuming. As an addition DB has huge amount of data.

Thanks in advance, Regards, Adrian

Does CROSS APPLY take too long - how much do you need to speed it up by? — mmmmmm, Sep 02 '11 at 14:09
If Ordering within each group isn't important then a CLR aggregate is probably fastest. If you need a specific order by `FOR XML` is the only reliable way (as long as your data doesn't contain certain problematic characters). — Martin Smith, Sep 02 '11 at 14:16
CROSS APPLY is quite confusing for me. I need to speed up query by >60% — Darj, Sep 02 '11 at 14:19
CROSS APPLY isn't consuming if used correctly. Can you show example code? Also, what version of SQL Server, as the answer given here will work in SQL Server 2005 and 2008, for example, but not SQL Server 2000. Finally, why do you want to do this? If you use this data later in SQL, it seems like a bad plan, and if it's for display, do it in the GUI/Client Application? — MatBailie, Sep 02 '11 at 14:24

score 8 · Accepted Answer · answered Sep 02 '11 at 14:27

here is the best concatenation method, it will not expand special characters like other XML methods:

--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

OUTPUT:

HeaderValue ChildValues
----------- ---------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

That can still blow up on some characters. e.g. `CHAR(11)` or `CHAR(27)` — Martin Smith, Sep 02 '11 at 14:42
@Martain, good point, but those are not very common, and never in any user input or data that I've seen in any of my DBs. — KM., Sep 02 '11 at 15:11
Good answer thank you. I've already +1'd your other life here stackoverflow.com/a/5031297/303101 — Reversed Engineer, Dec 07 '16 at 11:28

El Ronnoco · Answer 2 · 2011-09-02T14:19:53.900

2

If your version of SQL supports FOR XML use

select n.[name],CONVERT(VARCHAR(MAX),(SELECT *
        FROM (
                SELECT  l.[lang] + ' , '  AS '*'
                FROM    tblLang l
                WHERE l.[name]=n.[name]
                ) x
        FOR XML PATH (''), TYPE
    )) AS [Language]

from tblName n

This assumes simple table like

tblName
-------
name VARCHAR

tblLang
-------
lang VARCHAR
name VARCHAR

and it also adds a trailing , :(

edited Sep 02 '11 at 14:19

answered Sep 02 '11 at 14:12

El Ronnoco

11,753
5
38
65

3

try this method on data that contains `>`, `<`, `&` or any other "special` characters and you will see that it expands them into `>`, `<`, and `&` which is probably not expected or what is wanted. See my answer for how to eliminate this using `.value` – KM. Sep 02 '11 at 14:31
@KM Interesting. To be honest I find FOR XML pretty baffling! But I have wrestled with it before to give me the answers I wanted which is what my post is based on... – El Ronnoco Sep 02 '11 at 14:40
until tsql has a proper CONCAT() aggergate function there isn't much you can do. – KM. Sep 02 '11 at 15:38

How to concatenate results in T-SQL into column?

2 Answers2

Linked