Skip to Main Content
  • Questions

  • Synthesize rows based on column values

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priyanka.

Asked: May 07, 2016 - 7:59 am UTC

Last updated: August 06, 2017 - 8:06 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I have one database table test.The structure of the table is:

Col1(varchar) Col2(number)
The table has 2 rows:

Abc 5
Def 6
I desire the output to be:

Col1
Abc
Abc
Abc
Abc
Abc
Def
Def
Def
Def
Def
Def
I need to write a single query without using plsql loops to acheive this output.Please help.

and Connor said...

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x varchar2(10), c int);

Table created.

SQL>
SQL> insert into t values ('A',5);

1 row created.

SQL> insert into t values ('B',10);

1 row created.

SQL>
SQL> select t.x
  2  from t,
  3       table(
  4         cast(multiset(select rownum r from   dual connect by level <= t.c )
  5          as sys.ODCINUMBERLIST));

X
----------
A
A
A
A
A
B
B
B
B
B
B
B
B
B
B

15 rows selected.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 06, 2017 - 12:28 am UTC

What is ODCINUMBERLIST here?
Connor McDonald
August 06, 2017 - 8:06 pm UTC

ODCINUMBERLIST is just one of the pre-supplied nested table type.

You could just as easily do:

create type MY_TYPE as table of number;

and it will do the same thing

A reader, August 06, 2017 - 12:28 am UTC


select t.x
2 from t,
3 table(
4 cast(multiset(select rownum r from dual connect by level <= t.c )
5 as sys.ODCINUMBERLIST));


What is ODCINUMBERLIST here?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.