I am encountering a problem with joining a table in SQL Server. I get more rows than I need in my view.
The table I join looks something like this:
| ID | other ID | Name |
|---|---|---|
| 1 | 1 | Bob |
| 2 | 1 | Max |
| 3 | 2 | Jim |
| 4 | 2 | Tom |
| 5 | 2 | Ron |
The new table should look like this:
| other ID | Names |
|---|---|
| 1 | Bob,Max |
| 2 | Jim,Tom,Ron |
In that way I don’t get a new row every time a new Name comes up, but it's the same "other" ID.
Can someone please help me solve this problem? Thank you.
You basically have to use a group by with some string aggregate functions
Select id, string_agg(name, ',' ) from table1
t1 Join table2 t2 on t1.id=t2.id group by id
If your DBMS is SQL Server(2017+) or Postgres, you can use STRING_AGG aggregate function to have the names that are associated to the same id in a one row. Here is an exmple
SELECT other_id,
STRING_AGG(Name, ',')
FROM table_name
GROUP BY other_id
ORDER BY other_id
