Query Optimization – To repeat a pattern in Oracle SQL

Introduction: I can do this in MS-Excel, it takes me 1 minute, but I m trying to get this in Oracle SQL

Here is my Code:

SELECT A.*, (CASE WHEN A.r = 1 then 'X1' when A.r = 2 then 'X2' when A.r = 3 then 'X3' when A.r = 4 
then 'X4' when A.r = 5 then 'X2' when A.r = 6 then 'X6' end) X FROM
(
    Select Rownum r
    From dual
    Connect By Rownum <= 6 ) A

This is the Output:

Now, what if I have to do it for 25000 numbers, meaning when (rownum <= 25000) currently I have it only for 6, Is there a better method to do this with out case statement?

Answer

If you want to repeat this pattern of 6 rows for the remaining rows, then you can do:

select t.*, 
       (case when mod(rownum, 6) = 5 then 'X2'
             else 'X' || (mod(rownum - 1, 6) + 1)
        end)
from t;

Advertisement

Answer