这是***的连接方法，它不会像其他XML方法一样扩展特殊字符：

   - 与FOR XML&消除控制/编码的字符扩展& 
 set nocount on; 
声明@YourTable表（RowID int，HeaderValue int，ChildValue varchar（5））
 insert into @YourTable VALUES（1,1，'CCC'）
 insert into @YourTable VALUES（2 ，2，'B&B'）
插入到@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 B 
 3< br>，A& Z 

（3 row（s）affected）
 

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

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)