sql join csv from second table

Question

I have first table t1

||ID||name||add||

|t1|a|kl|

second table t2

||id||t1ID||Photo

|1|t1|image url

|2|t1|image url

I want to get row from first table and then csv of photo from second table

I ran below query

select sp.*, STUFF(( SELECT ',' + photo
                FROM t2
              FOR
                XML PATH('')
              ), 1, 1, '') as Photo from t2 sp
    left join t2 ig on sp.idID =  ig.t1id  
     where sp.id = 4

It returns correct value but two rows, since second column have two rows for first table t1 id.

how should I can only one row means for each row of first table it should return csv photo in last column

THanks

https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 among others. — Tab Alleman, Jan 04 '18 at 19:12

score 2 · Accepted Answer · answered Jan 04 '18 at 19:05

2

Try the following query;

select sp.*, STUFF(( SELECT ',' + photo FROM t2 
where t1id = sp.idID FOR XML PATH('')), 1, 1, '') as Photo from t1 sp
where sp.id = 4

answered Jan 04 '18 at 19:05

lucky

12,734
4
24
46

sql join csv from second table

1 Answers1