Skip to Main Content
  • Questions

  • Reading rows ahead of a particular value

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 04, 2017 - 9:49 pm UTC

Last updated: June 13, 2017 - 7:54 am UTC

Version: 11.2 and 12.1

Viewed 1000+ times

You Asked

Hello,

I have data in a single column of a table.

create table t (ctext varchar2(255));

insert into t values ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP');
insert into t values (' PUBLICATION_CELLS ');
insert into t values ('2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3');
insert into t values (' PUBLICATION_SUBSECTIONS ');
insert into t values (' PUBLICATION_SECTIONS ');
insert into t values (' TEXT_STRINGS ');
insert into t values (' TRANSLATION_PACKAGES ');
insert into t values ('2017-06-04 17:17-28zsy1aq1zpug-GSAAPP');
insert into t values (' PUBLICATION_SECTIONS ');
insert into t values (' USERS ');
insert into t values (' MASTER_CELLS ');
insert into t values (' SYSKEY_WORK ');
insert into t values (' TELLTALE_COLOR_STR ');

From the above table, I want the output like below:

2017-06-04 17:17-4qxzmh15zwv35-GSAAPP, PUBLICATION_CELLS
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3, PUBLICATION_SUBSECTIONS|PUBLICATION_SECTIONS|TEXT_STRINGS|TRANSLATION_PACKAGES
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP, PUBLICATION_SECTIONS|USERS|MASTER_CELLS|SYSKEY_WORK|TELLTALE_COLOR_STR

The "|" used is just a delimiter to separate out values.

Thanks,



and Connor said...

You are assuming an implicit to the order (ie, the insertion order) but that does not really exist, so we need *something* to sequence the rows. So I've added an ID column. Once that is there, the solution is relatively straight forward


SQL>
SQL> create table t (ctext varchar2(60), id int default seq.nextval);

Table created.

SQL>
SQL> insert into t (ctext) values ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_CELLS ');

1 row created.

SQL> insert into t (ctext) values ('2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SUBSECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' TEXT_STRINGS ');

1 row created.

SQL> insert into t (ctext) values (' TRANSLATION_PACKAGES ');

1 row created.

SQL> insert into t (ctext) values ('2017-06-04 17:17-28zsy1aq1zpug-GSAAPP');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' USERS ');

1 row created.

SQL> insert into t (ctext) values (' MASTER_CELLS ');

1 row created.

SQL> insert into t (ctext) values (' SYSKEY_WORK ');

1 row created.

SQL> insert into t (ctext) values (' TELLTALE_COLOR_STR ');

1 row created.

SQL> select * from t;

CTEXT                                                                ID
------------------------------------------------------------ ----------
2017-06-04 17:17-4qxzmh15zwv35-GSAAPP                                 1
 PUBLICATION_CELLS                                                    2
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3                                3
 PUBLICATION_SUBSECTIONS                                              4
 PUBLICATION_SECTIONS                                                 5
 TEXT_STRINGS                                                         6
 TRANSLATION_PACKAGES                                                 7
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP                                 8
 PUBLICATION_SECTIONS                                                 9
 USERS                                                               10
 MASTER_CELLS                                                        11
 SYSKEY_WORK                                                         12
 TELLTALE_COLOR_STR                                                  13

13 rows selected.

SQL>
SQL>
SQL> select listagg(ctext,'|') within group ( order by x, id )
  2  from (
  3  select last_value(rn ignore nulls) over ( order by id) x,
  4         id, ctext
  5  from (
  6  select
  7    case when ctext like '20%' then row_number() over ( order by id ) end as rn,
  8    id,
  9    ctext
 10  from t
 11  )
 12  )
 13  group by x
 14  order by x
 15  ;

LISTAGG(CTEXT,'|')WITHINGROUP(ORDERBYX,ID)
----------------------------------------------------------------------------------------------------------------------------------
2017-06-04 17:17-4qxzmh15zwv35-GSAAPP| PUBLICATION_CELLS
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3| PUBLICATION_SUBSECTIONS | PUBLICATION_SECTIONS | TEXT_STRINGS | TRANSLATION_PACKAGES
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP| PUBLICATION_SECTIONS | USERS | MASTER_CELLS | SYSKEY_WORK | TELLTALE_COLOR_STR

3 rows selected.

SQL>


Rating

  (2 ratings)

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

Comments

A reader, June 05, 2017 - 2:28 pm UTC

That is pretty intuitive. Thanks a lot.


FU WTF R U SOL

To ch, June 13, 2017 - 1:43 am UTC

FU WTF RU SOL
Connor McDonald
June 13, 2017 - 7:54 am UTC

Those features dont become available until patchset 17.

You'll need to wait for that.
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.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.