how to write a query to get the matrix report in Oracle SQL?

Question

suppose if i give '12345'

output should be:

1
12
123
1234
12345
1234
123
12
1

Answer 1

Method 1: Using a subquery

    SELECT *
    FROM   emp
    WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                         FROM   emp);

Method 2: Use dynamic views;

    SELECT *
    FROM   ( SELECT rownum rn, empno, ename
             FROM emp
           ) temp
    WHERE  MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING

SELECT rownum, f1
FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n