How can I achieve this table?
Here are the tables
And here is the query that I have come-up but I'm still having some error
SELECT DISTINCT Table1.ID as T1_ID,
(SELECT *
from Table1.Location
where Table1.ID = Table1.ID) as T1_Location,
Table2.Location as T2_Location
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2 = ID
I tested in SQL Flow with Oracle, it seems works
SELECT table1.ID, GROUP_CONCAT(table1.Location), table2.Location FROM table2 INNER JOIN table1 ON table2.ID = table1.ID GROUP BY table1.ID
Assuming a concatenated string is okay for the Table1.Location output, you can do something like:
SELECT t1.id, CONCAT(t1.location, '-'), t2.location
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.id = t2.id
GROUP BY t1.id, t2.location
Syntax may vary slightly depending on your RDBMS
You need an additional column to uniquely identify records on Table1, otherwise you won't have control over which record is matched, or you can sort alphabetical or maybe use ROW_NUMBER(). If you don't care which record is selected from table 1, the query bellow will do.
WITH tmpT1 AS (SELECT DISTINCT ID, Location FROM Table1)
SELECT t1.ID AS T1_ID, t1.location AS T1_Location, t2.Location AS T2_Location FROM Table2 AS t2 JOIN tmpT1 AS T1
ON t2.ID = T1.ID
Table1.location? 3 strings concatenated? An array like structure (if the dB supports it)?GROUP BY, but it will result in the second column being a concatenated string with the locations from Table1.