Aggregate values to separate columns from using WM

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

CodePudding user response：

For such an output, you don't even need table1 as all columns are already contained in table2. If you apply conditional aggregation, you get the result.

Sample data:

SQL> with
  2  table1 (appid, description) as
  3    (select 1, 'Sample' from dual),
  4  table2 (addid, appid, address) as
  5    (select 1, 1, 'Add1' from dual union all
  6     select 2, 1, 'Add2' from dual union all
  7     select 3, 1, 'Add3' from dual union all
  8     select 4, 1, 'Add4' from dual
  9    )

Query:

 10  select b.appid,
 11    max(case when b.addid = 1 then address end) address1,
 12    max(case when b.addid = 2 then address end) address2,
 13    max(case when b.addid = 3 then address end) address3
 14  from table2 b
 15  group by b.appid;

     APPID ADDRESS1   ADDRESS2   ADDRESS3
---------- ---------- ---------- ----------
         1 Add1       Add2       Add3

SQL>

Also, forget that wm_concat exists. It is undocumented function and - although it works in certain database versions - once you upgrade to 12c (or higher), your code won't work any more because Oracle removed wm_concat. Switch to something else (listagg if possible, xmlagg otherwise).

CodePudding user response：

Do not use WM_CONCAT. It is an undocumented function that was removed in Oracle 12 and no longer exists.

Number the rows using the ROW_NUMBER analytic function and then PIVOT:

SELECT *
FROM   (
  SELECT t1.AppID,
         Address,
         ROW_NUMBER() OVER (PARTITION BY t1.AppID ORDER BY t2.AddID) AS rn
  FROM   table1 t1
         LEFT OUTER JOIN table2 t2
         ON (t1.appid = t2.appid)
)
PIVOT (
  MAX(Address) FOR rn IN (
    1 AS address1,
    2 AS address2,
    3 AS address3
  )
);

Or, number the rows and then use conditional aggregation:

SELECT appid,
       MAX(CASE rn WHEN 1 THEN address END) AS address1,
       MAX(CASE rn WHEN 2 THEN address END) AS address2,
       MAX(CASE rn WHEN 3 THEN address END) AS address3
FROM   (
  SELECT t1.AppID,
         Address,
         ROW_NUMBER() OVER (PARTITION BY t1.AppID ORDER BY t2.AddID) AS rn
  FROM   table1 t1
         LEFT OUTER JOIN table2 t2
         ON (t1.appid = t2.appid)
)
GROUP BY appid

Which, for the sample data:

CREATE TABLE table1 (AppID PRIMARY KEY, "DESC") AS
SELECT 1, 'Sample' FROM DUAL;

CREATE TABLE table2 (AddID PRIMARY KEY, AppID, Address) AS
SELECT 1, 1, 'Add1' FROM DUAL UNION ALL
SELECT 2, 1, 'Add2' FROM DUAL UNION ALL
SELECT 3, 1, 'Add3' FROM DUAL UNION ALL
SELECT 4, 1, 'Add4' FROM DUAL;

Both output:

APPID ADDRESS1 ADDRESS2 ADDRESS3

1 Add1 Add2 Add3

db<>fiddle here