How to flatten nested tables into a new row?

Question

I have a table where one of the columns contains a nested table of single row.

mytable 
title | col
a     | {1, 2}
b     | {3}
c     | NULL

What I need to do is flatten the nested table into a comma-separated String.

Result:
{
    a: "1, 2"
    b: "3"
    c: NULL
}

For my specific purpose I can't just have the result be in table form (the above is very simplified, but it would solve my issue). The closest I think I've gotten is with the following statement (which just returned 1, 2, 3, null).

select t.* 
from mytable, table(mytable.col)(+) t;

I've tried listagg, but couldn't get it to work for my case. I'm currently trying to read more into nested tables, but it has been slow going, and I haven't been able to find anything for this specific issue (nested tables).

score 4 · Accepted Answer · answered Aug 01 '18 at 21:06

Does this do what you need? What do you mean listagg wasn't working for your purpose?

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
       NESTED TABLE col1 STORE AS col1_tab;

INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));

 SELECT TMP.id,
        listagg(Column_Value,',') 
          WITHIN GROUP(ORDER BY Column_Value)
   FROM (SELECT id, 
                COLUMN_VALUE 
           FROM nested_table t1, 
                TABLE(t1.col1) t2
        ) TMP
  GROUP
     BY id

http://sqlfiddle.com/#!4/90cfb6

This is exactly what I needed, thanks. What I meant by saying that I couldn't get listagg to work was that I couldn't get the syntax right. — billoot, Aug 01 '18 at 23:13

How to flatten nested tables into a new row?

1 Answers1