Subquery returning csv written values in a Main Query Column, possible?

Question

I got to tables.

TABLE 1: [..fields..] [CATEGORIE] [..fields..]

TABLE 2: [..fields..] [ID] [CATEGORIE] [..fields..]

I want to connect a bit special and tried it like this:

SELECT [..other fields..], CATEGORIE, (SELECT ID FROM TABLE2 WHERE TABLE2.CATEGORIE = TABLE1.CATEGORIE) FROM TABLE1;

I want to have the IDs of the SubQuery in on Column of the Main Query

like that ( [] are representing columns)

[resultfield1] [resultfield2] [resultfield3] [ID1,ID2,ID3,ID4,...]

is there a way to afford it?

Help is very appreciated,

thanks in advance

This has been asked many many times before. Just do a quick search with the words GROUP, CONCATENATE and SQL-SERVER — Lamak, Sep 20 '11 at 14:24
@Lamak This has been asked so many times that I created the `sql-server-group-concat` tag. It's like the `greatest-n-per-group` tag. It keeps coming back. — Adriano Carneiro, Sep 20 '11 at 14:42
thx @Lamak, I didn't know theese terms, but issue is solved after this answers / comments, thx to all — Harry, Sep 20 '11 at 14:47

score 5 · Accepted Answer · edited May 23 '17 at 12:25

5

As hard as it is to read understand your question, what you want is to use FOR XML PATH:

select 
    categorie, 
    stuff((select ', ' + id
           from table2 t2 where t1.categorie = t2.categorie
           for xml path('')),
          1,2,'') [IDs]
from table1 t1

Subquery returning csv written values in a Main Query Column, possible?

1 Answers1