Oracle SQL - Generate rows based on quantity column

Question

We use pooled positions that have a max headcount assigned and I need to build a report that creates a line for each head, including the details of the incumbent if there is one or a NULL line where there is a vacancy.

Like this:

Position_Title	Headcount	Incumbent
Analyst	3	Employee1
Analyst	3	Employee2
Analyst	3

question from:https://stackoverflow.com/questions/65903989/oracle-sql-generate-rows-based-on-quantity-column

Answer 1

You can use multiset to generate rows as per the headcount column as follows:

SELECT 
HAP.NAME POSITION_TITLE,
HAP.POSITION_CODE,
PGF.NAME GRADE_NAME,
PGF.GRADE_CODE,
HAP.ACTIVE_STATUS,
HAP.POSITION_TYPE,
HAP.HEADCOUNT,
PAAM.ASSIGNMENT_NUMBER,
Lvls.Column_value row_num

FROM
HR_ALL_POSITIONS_F_VL HAP, 
PER_GRADES_F_VL PGF,
PER_ALL_ASSIGNMENTS_M PAAM,
table(cast(multiset(select level from dual connect by  level <= hap.headcount) as sys.OdciNumberList)) lvls
WHERE 

    HAP.ENTRY_GRADE_ID = PGF.GRADE_ID
AND PAAM.POSITION_ID(+) = HAP.POSITION_ID
AND      TRUNC(Sysdate) between HAP.effective_start_date AND HAP.effective_end_date
AND      TRUNC(Sysdate) between PGF.effective_start_date AND PGF.effective_end_date
AND      PAAM.effective_start_date(+) <= TRUNC(Sysdate)
AND      PAAM.effective_end_date(+) >= TRUNC(Sysdate);

Note: add the condition as per your requirement and always use standard ANSI joins.