How to join all data in table for one column as string with sql?

Question

I want to join all data in table. For example: I have a table like this,

ID     Name    ForeingId
----------------------
1       A        1
2       B        1
3       C        2
4       D        1
5       E        1

I want to get as a result the following with query. Such as 'SELECT ... WHERE ForeingId=1' . I dont want to use procedure or function.

result : A,B,D,E

You can look at this link, http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — mehul9595, Apr 25 '12 at 06:03
Sorry, I forgot to say. I am using MSSQL . Edited my question. — Sinan AKYAZICI, Apr 25 '12 at 06:17

score 0 · Answer 1 · answered Apr 25 '12 at 06:02

0

For ORACLE, try this:

SELECT 
   LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS NAME
  FROM  TABLE
WHERE ForeingId= '1'

answered Apr 25 '12 at 06:02

bonsvr

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

I am using MSSQL

I thank everyone for the answers. I found answer with link of @mehul9595's comment.

This way:

SELECT STUFF(
    (SELECT ',' + t.name FROM table_name t
     where t.foreingId = 1 FOR XML PATH ('')), 1, 1, '')

edited May 23 '17 at 10:24

Community

answered Dec 27 '12 at 12:38

Sinan AKYAZICI

2 Answers2