how to join two tables in sql with respect to same value comma seperated

Question

The question may be confusing but here's the example.

TABLE 1

|ID|  
1
1
2
2
3

TABLE 2

|ID| |NAME|
1     ROSE
1     LILLY
2    JASMINE
2    SUNFLOWER
3     LAVENDER

I want the output like this.

JOINED_TABLE

|ID|      |NAME|
1         ROSE, LILLY
2         JASMINE, SUNFLOWER
3         LAVENDER

Can Anyone please suggest me the query in SQL?

SQL Server 2000? SQL Server 2019? Something in between? Have you searched for anything? Have you tried anything? Why does `TABLE 1` have to be involved at all? — Aaron Bertrand, Mar 01 '22 at 12:46
Hi, Thanks for your reply. Its server 2017. I want to join two tables based on the same value. If TABLE 1 has multiple occurrences, I want to join TABLE 2 with the value associated with table one. We can do it with the normal join but I want the column values to be in the same column with commas separated. — Sravanthi Goud, Mar 01 '22 at 12:52
My point was that you've simplified your sample data so much you've made it hard to understand why a join is even necessary (since a join can't possibly change the output). Also please always tag with the version of SQL Server you're using (otherwise we have to guess or ask you). — Aaron Bertrand, Mar 01 '22 at 12:54
Only reason I can think of including `Table 1` would be in an `EXISTS` with the definitions we have. — Thom A, Mar 01 '22 at 12:55

score 1 · Answer 1 · answered Mar 01 '22 at 12:47

1

Table1 isn't needed in your example. You can use string_agg to get the desired output:

select id, string_agg(name, ', ')
from table2
group by id;

answered Mar 01 '22 at 12:47

Zakaria

1

Hey, Thank you so much. Your answer made my day. – Sravanthi Goud Mar 01 '22 at 13:01

1 Answers1