How to returns number of rows incrementally based on column values

I have departments and issues tables. For every department there are approval levels. So if say HR department has 3 approval levels, i want the drop down to return a new alias column as Y1,Y2,Y3. Similarly if finance has 2 it should return Y1 and Y2. Is it possible in sql?

As of now the first alias column is returning say Y3 for HR, but i want that split in rows Y1,Y2,Y3. is it possible via sql?

Answer

Generate a sequence from 1 to the maximum approval levels in a CTE.

WITH CTE as (
    SELECT LEVEL n
    FROM DUAL
    CONNECT BY LEVEL <= (select MAX(approval_level) from p_it_Departments )
)
SELECT      'Y'||c.n as approval
            ,d.approval_level
            ,d.dept_name
FROM        p_it_issues i 
INNER JOIN  p_it_Departments d  ON i.related_dept_id=d.dept_id
INNER JOIN  CTE c               ON c.n <= d.approval_level
ORDER BY dept_name

You could also add a DISTINCT to the last SELECT to eliminate the duplicates that were present in your original query as well.