How to convert second select statement result to CSV String in main select statement ?

Question

How to convert second select statement result to CSV String in main select statement ?

assume following SQL statement:

SELECT A1,A2, (SELECT aCol FROM Table2) as A3
FROM Table1

Now, I want to convert result of SELECT aCol FROM Table2 to CSV string that show at A3 field. such as :

A1Value | A2Value | A3Value1, A3Value2, A3Value3

thanks.

---

Update :

Solution for MySql

Answer 1

You are aware that without correlation, you'll get the same comma separated list for every row returned?

For MySQL

Use the GROUP_CONCAT fuction:

SELECT t.a1,
            t.a2,
            (SELECT GROUP_CONCAT(t2.acol)
                FROM TABLE_2 t2) AS A3
   FROM TABLE_1 t

Assuming SQL Server 2005+

Use:

SELECT t.a1,
            t.a2,
            STUFF(ISNULL(SELECT ', '+x.acol                
                                   FROM TABLE_2 x 
                            GROUP BY x.acol             
                              FOR XML PATH ('')),''),1,2,'')
  FROM TABLE_1 t