Write the array function from Postgres in Oracle Syntax

Question

In Postgres, I would do this:

SELECT main.*
    , array(SELECT columnA FROM tableB alt WHERE alt.columnB = main.columnB) AS columnAs_to_tableA
FROM tableA main

How might one do the same in Oracle 10? It should be noted that I cannot use the listagg. I found this answer to a similar question: Aggregate String Concatenation in Oracle 10g I have NO clue how this answer is working at all, nor if this really is the "correct" solution, because it was never marked as answered/accepted.

I feel like answering this with an example using the same table names I provided for a one-to-one comparison might work best for others seeking the same answer.

Thank You

EDIT 1: I should add that I want to avoid introducing new schema elements, like tables, functions, etc...

EDIT 2: Removing the requirement for the array. a comma delimited string will suffice.

Why do you want to avoid introducing a collection type? Could you potentially leverage an existing collection type? You can't return a collection without a collection type. And if you could, the consumer wouldn't be able to do anything with it without knowing what sort of collection you're returning. Could you return a cursor rather than a collection? — Justin Cave, Aug 03 '15 at 18:37
I see what you mean... Perhaps I will just have to convert to a comma delimited string and deal with it in the code base. I will update the question to reflect this. Again, thank you for your prompt responses! — brightmatter, Aug 03 '15 at 21:04
If you just want a comma separated list, see any of these posts: http://stackoverflow.com/search?q=[oracle]+group_concat — , Aug 03 '15 at 21:18

Justin Cave · Accepted Answer · 2015-08-05T01:20:37.473

If you want to return a collection, you'd need to create a type and use collect to populate the type.

CREATE TYPE typ_columnA_nt
    AS TABLE OF <<columnA data type>>

Then you can use the collect function

SELECT main.*
    , cast( collect(SELECT columnA 
                      FROM tableB alt 
                    WHERE alt.columnB = main.columnB)
            as typ_columnA_nt ) AS columnAs_to_tableA
FROM tableA main

If you want to return a cursor, you can use the cursor function

SELECT main.*,
       cursor( SELECT columnA
                 FROM tableB alt
                WHERE alt.columnB = main.columnB ) as columnAs_to_tableA
  FROM tableA main

If you want to return a comma-separated string, Tim Hall has a canonical list of string aggregation techniques in Oracle. In versions where listagg is not an option, my preference would be to create a user-defined aggregate function which would allow you to

select main.*,
       (select string_agg(columnA)
          from tableB alt
         where alt.columnB = main.columnB) as columnAs_to_tableA
  from tableA main

If you are limited to solutions that don't involve creating new objects, the simplest option would be to use wm_concat though this is not officially supported

select main.*,
       (select wm_concat(columnA)
          from tableB alt
         where alt.columnB = main.columnB) as columnAs_to_tableA
  from tableA main

If you can't create any supporting objects and you can't use unsupported functions, then you're stuck with the old row_number and sys_connect_by_path option which is more than a bit ugly. I think you'd want something like this but there is a decent chance that I've made a small syntax error.

select main.*,
       agg.value_string
  from tableA main
       left join (select alt_outer.columnB,
                         ltrim(max(sys_connect_by_path(alt_outer.columnA,','))
                                  keep( dense_rank last order by alt_outer.columnA ),
                               ',') as value_string
                    from (select alt.columnA,
                                 alt.columnB,
                                 row_number() over (partition by alt.columnB
                                                        order by alt.columA) as curr,  
                                 row_number() over (partition by alt.columnB
                                                        order by alt.columA) -1 as prev
                            from tableB alt) alt_outer
                    group by alt_outer.columnB
                  connect by alt_outer.prev = prior alt_outer.curr
                         and alt_outer.columnB = prior alt_outer.columnB
                    start with alt_outer.curr = 1) agg
         on( main.columnB = agg.columnB )

Yet another option would be to use the XML functions

SELECT main.* , 
       tableB_alt.list AS columnAs_to_tableA 
  FROM tableA main 
       LEFT JOIN ( SELECT columnB ,
                          TRIM(TRAILING ',' 
                                 FROM 
                                 XMLAGG(
                                   XMLELEMENT(E,columnA||',')
                                 ).EXTRACT('//text()')
                              ) list 
                     FROM tableB 
                    GROUP BY columnB ) tableB_alt 
         ON tableB_alt.columnB = main.columnB

I have moved the target a bit due to your answer. Thank you for your fast response, and helping me to narrow the acceptance criteria. — brightmatter, Aug 03 '15 at 18:24

Write the array function from Postgres in Oracle Syntax

1 Answers1