SQL Combine rows with same ID

Question

I have two tables and I need to combine the rows.

As far I have:

$sql = "SELECT A.AID, A.Street, B.FirstName, B.LastName                                     
        FROM A 
        LEFT JOIN B 
        ON A.AID = B.AID;";

Could you help me please to merge those rows, where several people live in the same street?

Might be simpler to do the merge in PHP, just sort the query by AID — RiggsFolly, Apr 14 '21 at 11:45
Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Charlieface, Apr 14 '21 at 12:20
The [`tsql`](https://stackoverflow.com/tags/tsql/info) tag covers Sybase ASE and Microsoft SQL Server. Do you require a solution that works on both? Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Images of data bad, DDL and consumable data good, ... . — HABO, Apr 14 '21 at 14:21

Gordon Linoff · Answer 1 · 2021-04-14T13:00:18.927

If you are using MySQL, then you would use GROUP_CONCAT() or a JSON aggregation function. The idea is:

SELECT A.AID, A.Street,
       GROUP_CONCAT(B.FirstName, ' ', B.LastName SEPARATOR ', ') as names                                 
FROM A LEFT JOIN
     B 
     ON A.AID = B.AID
GROUP BY A.AID, A.Street;

In SQL Server you would use string_agg():

SELECT A.AID, A.Street,
       STRING_AGG(CONCAT(B.FirstName, ' ', B.LastName), ', ') as names                                 
FROM A LEFT JOIN
     B 
     ON A.AID = B.AID
GROUP BY A.AID, A.Street;

SQL Combine rows with same ID

1 Answers1