How to print pattern output in Oracle's SQL-CodePudding

I have a table with below data

Table : emp

name
A
B
C
D

Expected Output :

names
A
AB
ABC
ABCD

I only know how to print data in horizontal form

SELECT LISTAGG(name,'|') 
  FROM emp;

Not sure how to cut the horizontal and get expected output

CodePudding user response：

You can use a hierarchical query:

SELECT REPLACE(SYS_CONNECT_BY_PATH(name, '|'), '|') AS names
FROM   ( SELECT ROW_NUMBER() OVER (ORDER BY name) AS rn,
                name
         FROM   emp )
START WITH rn = 1
CONNECT BY PRIOR rn   1 = rn;

Which, for the sample data:

CREATE TABLE emp (name) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'B' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'D' FROM DUAL;

Outputs:

NAMES

A

AB

ABC

ABCD

db<>fiddle here

CodePudding user response：

Here's one option:

SQL> with test (col) as
  2    (select 'A' from dual union all
  3     select 'B' from dual union all
  4     select 'C' from dual union all
  5     select 'D' from dual
  6    ),
  7  temp as
  8    (select listagg(col) within group (order by col) val,
  9            count(*) cnt
 10     from test
 11    )
 12  select substr(val, 1, level) result
 13  from temp
 14  connect by level <= cnt;

RESULT
----------
A
AB
ABC
ABCD

SQL>

Basically, in temp CTE aggregate column values (and find number of rows), and then fetch substring in a hierarchical query which has as many levels as there are rows in the table.