How to get rows to one column during creating a view

Question

I have the problem that I want to join several tables to create a new view. The view is working fine so far but I struggle with the following problem:

I have a table name it 'MyTable1'. In this table there is an ID. I have a second table name it 'MyTable2'. This table is referenced to 'MyTable1' with the ID column.

MyTable1:

ID
567

MyTable2:

ID    MyTable1_ID    object_ID
1     567            896
2     567            967
3     567            756

Code:

SELECT
MyTable1.ID,
MyTable2.ID,
MyTable2.object_ID
FROM
MyTable1
LEFT JOIN MyTable2 ON MyTable2.MyTable1_ID = MyTable1.ID

Output:

MyTable1.ID      MyTable2.ID     MyTable2.object_ID
567              1               896
567              2               967
567              3               756

Desired Output:

MyTable1.ID      MyTable2.ID     MyTable2.object_ID
567              1;2;3           896;967;756

I have tried with LISTAGG but also get only 3 rows as output:

SELECT
MyTable1.ID,
MyTable2.ID,
MyTable2.object_ID
CASE WHEN (SELECT count(*) FROM MyTable2 JOIN MyTable1 ON MyTable2.MyTable1_ID = MyTable1.ID having count(*) > 1) > 1 THEN LISTAGG(MyTable2.object_id, '; ') WITHIN GROUP (order by MyTable2.object_id) ELSE MyTable2.object_id END
FROM
MyTable1
LEFT JOIN MyTable2 ON MyTable2.MyTable1_ID = MyTable1.ID

Can anybody help me?

Thanks

*I have tried with LISTAGG but also get only 3 rows as output* - so please provide your code with `listagg` — astentx, Mar 14 '23 at 12:30
You need to `listagg` both `MyTable2.ID` and `MyTable2.object_id` regardless of the count — astentx, Mar 14 '23 at 13:01

Bogdan Dincescu · Answer 1 · 2023-03-14T13:25:38.413

2

SELECT
MyTable1.ID,
listagg(MyTable2.ID,';') within group(order by MyTable2.ID) tab2_id
listagg(MyTable2.object_ID,';') within 
   group(order by MyTable2.object_ID) tab2_object_id
FROM
MyTable1
LEFT JOIN MyTable2 ON MyTable2.MyTable1_ID = MyTable1.ID
group by MyTable1.ID
order by 1;

Yet, beware, listagg will work if the concatenation wil not be longer than 4000 chars.

So, in Oracle livesql I ran the below:

with tab1 (id) as (
  select 567 from dual
)
,tab2 (id,tab1_id,object_id) as (
  select 1, 567, 896 from dual union all
  select 2, 567, 998 from dual union all
  select 3, 567, 777 from dual
)
select tab1.id
 ,listagg(tab2.id,';') within group(order by tab2.id) tab_ids
 ,listagg(tab2.object_id,';') within group(order by tab2.object_id) 
tab_object_ids 
from tab1
left join tab2 on tab1.id=tab2.tab1_id
group by  tab1.id
order by 1

and running that it showed me only one row. Can't figure out how you say it fetched three rows for you.

edited Mar 14 '23 at 13:25

answered Mar 14 '23 at 12:35

Bogdan Dincescu

683
6

This gaves me again 3 lines – BiSaM Mar 14 '23 at 12:48
@SaMat It cannot because the result is aggregated by `MyTable1.ID`, so you will have a single row per value of this column – astentx Mar 14 '23 at 13:04
Sorry, yes got it. forgot to group by ID. BUt I face problems when I also need to add MyTable2.ID in the output – BiSaM Mar 14 '23 at 13:24
@SaMat One question per question please. Either provide enough sample data and desired output for it along with a complete description of the requirement, or try to adopt the solution for your particular case and ask a question about actual issues. – astentx Mar 14 '23 at 13:38
@astentx Sorry but this is something I wrote in my original question as well. – BiSaM Mar 14 '23 at 13:55

score 2 · Accepted Answer · answered Mar 14 '23 at 15:22

With sample data provided:

WITH
    t1 AS
        (   Select 567 "ID" From Dual   ), 
    t2 AS
        (
            Select 1 "ID", 567 "MY_T1_ID", 896 "OBJECT_ID" From Dual Union All
            Select 2 "ID", 567 "MY_T1_ID", 967 "OBJECT_ID" From Dual Union All
            Select 3 "ID", 567 "MY_T1_ID", 756 "OBJECT_ID" From Dual 
        )

If you could use LISTAGG() analytic function without OVER(Partition By t1.ID):

Select  t1.ID, 
        LISTAGG(t2.ID, ';') WITHIN GROUP (Order By t2.ID)  "MY_T2_ID",
        LISTAGG(t2.OBJECT_ID, ';') WITHIN GROUP (Order By t2.ID) "MY_T2_OBJECT_ID"
From    t1
Inner Join t2 ON(t2.MY_T1_ID = t1.ID)

Result:
ID   MY_T2_ID  MY_T2_OBJECT_ID
---  --------  ---------------
567  1;2;3     896;967;756

The more likely there would be a need to partition by t1.ID then you should use DISTINCT keyword to exclude duplicate rows (could be performance costly with big datasets):

Select  DISTINCT t1.ID, 
        LISTAGG(t2.ID, ';') WITHIN GROUP (Order By t2.ID)  OVER (Partition By t1.ID) "MY_T2_ID",
        LISTAGG(t2.OBJECT_ID, ';') WITHIN GROUP (Order By t2.ID) OVER (Partition By t1.ID) "MY_T2_OBJECT_ID"
From    t1
Inner Join t2 ON(t2.MY_T1_ID = t1.ID)

If that is the case you could do it like here:

Select ID, Max(MY_T2_ID) "MY_T2_ID", Max(MY_T2_OBJECT_ID) "MY_T2_OBJECT_ID"
From
    (
        Select  t1.ID, 
                LISTAGG(t2.ID, ';') WITHIN GROUP (Order By t2.ID)  OVER (Partition By t1.ID) "MY_T2_ID",
                LISTAGG(t2.OBJECT_ID, ';') WITHIN GROUP (Order By t2.ID) OVER (Partition By t1.ID) "MY_T2_OBJECT_ID"
        From    t1
        Inner Join t2 ON(t2.MY_T1_ID = t1.ID)
    )
Group By ID

Result:
ID   MY_T2_ID  MY_T2_OBJECT_ID
---  --------  ---------------
567  1;2;3     896;967;756

`select distinct , over (partition by )` is exactly the same as `select , ... group by `, because you will always have the same value of analytic function per each `partition by` value (given there's no `order by` as in the query above). So above queries are equivalent and the first one is more readable and straightforward — astentx, Mar 15 '23 at 11:27
@astentx There is a difference in performance when dealing wit big datasets. For first one Cost (%CPU) is 10 (20) and for second one 9 (12). Distinct is more demanding then Group By. Beside that, yes, you are right, they are the same. — d r, Mar 15 '23 at 11:47
It's not very clear what may cause performance difference (especially without showcase setup). Physically `distinct` builds hashes on all the columns while `group by` does the same for `group by` columns. Aggregate functions performs the same work in both cases as long as aggregation input specified in `partition by` is exactly the same as in `group by` and Oracle has to do the same hashing to divide rows into partitions. So there's no obvious reason to perform less aggregation and do less work in case of analytic function compared to aggregate function. — astentx, Mar 15 '23 at 14:26

How to get rows to one column during creating a view

2 Answers2