I have these tables and values:
Table1:
AppID | Desc |
---|---|
1 | Sample |
Table2:
AddID | AppID | Address |
---|---|---|
1 | 1 | Add1 |
2 | 1 | Add2 |
3 | 1 | Add3 |
4 | 1 | Add4 |
With my current code:
SELECT app.AppID
add.Address as Addresses
FROM Table1 app
LEFT JOIN (SELECT AppID,
REPLACE(WM_CONCAT(Address), ',', '; ') AS Address
FROM Table2
GROUP BY AppID) add ON app.AppID = add.AppID
ORDER BY app.AppID;
I get this output:
AppID | Addresses |
---|---|
1 | Add1; Add2; Add3; Add4 |
But how can I possibly have this output instead?:
- Put the addresses in separate columns instead of being joined in one column
- Only limit the column for address to only 3.
DESIRED OUTPUT:
AppID | Address1 | Address2 | Address3 |
---|---|---|---|
1 | Add1 | Add2 | Add3 |
I am using Oracle Developer v3.2.10