Merge two SQL query into one row

Question

I have two tables in my database:

Table1:

|   id   |   name     |
+--------+------------+
|  100   |   john     |
|  200   |   Ali      |

Table2:

|  id  |  account  |  bank  |
+------+-----------+--------+
| 100  |  AAAAAAA  |  bnk1  |
| 100  |  BBBBBBB  |  bnk2  |
| 200  |  XXXXXXX  |  bnk1  |

This means "john" has two different accounts in two different banks

Now I want a query to show this:

|  id  |            tab2            |
+------+----------------------------+
| 100  | AAAAAAAbank1 - BBBBBBBbnk2 |
| 200  |        XXXXXXXbnk1         |

Is it possible?

Yes, it's possible. But it's not good practice to do this on the database, and the exact mechanism (sql code) you use will vary widely depending on which database platform you have (you need to add the appropriate tag). — Joel Coehoorn, Jul 31 '17 at 16:48
Possible duplicate of [Concatenate row values T-SQL](https://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql) — Beltaine, Jul 31 '17 at 19:00
How do you know you want `AAAAAAAbank1 - BBBBBBBbnk2` for `id` = 100, and not `BBBBBBBbnk2 - AAAAAAAbank1 `? Remember: tables are *unordered* sets of records. You need a way to determine that order. — Bacon Bits, Jul 31 '17 at 19:12
This is a very solvable problem... but only if you tell us what database you are using. — SandPiper, Jul 31 '17 at 20:26
List_Agg() (Oracle), For XML Path (SQL Server), Group_Concat() (mySQL) are the functions you use to combine rows into one column. other RDBMS may use one of the above or something different I think new version of SQL Server may even offer something new. — xQbert, Jul 31 '17 at 20:34

score 0 · Answer 1 · answered Jul 31 '17 at 20:28

Assuming you are using MS SQL Server

SELECT  DISTINCT
        Table1.id,
        LEFT(STUFF((SELECT account + bank + '-' FROM Table2 WHERE id = Table1.id FOR XML PATH ('')), 1, 1, ''),LEN(STUFF((SELECT account + bank + '-' FROM Table2 WHERE id = Table1.id FOR XML PATH ('')), 1, 1, ''))-1)
FROM    Table1
        JOIN Table2
            ON Table1.id = Table2.id

Merge two SQL query into one row

1 Answers1