CODEWITHSUNDEEP
sqloracle-database
Velocity
Velocity

Reputation: 479

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?

Upvotes: 0

Views: 33

Answers (2)

Gabriel Durac
Gabriel Durac

Reputation: 2760

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.

Upvotes: 1

Velocity
Velocity

Reputation: 479

Ok, this would not have been mentioned in comment properly but i figured it out so wanted to share.

with cte as(
SELECT 
  ROW_NUMBER() OVER(partition by d.dept_name ORDER BY d.dept_name ASC ) AS Row#, 
  d.approval_level, d.dept_name
FROM  p_it_issues i, p_it_Departments d where i.related_dept_id=d.dept_id
) 
select 'Y'||cte.Row# from cte;

This would print what i wanted to display.

Upvotes: 0

Related Questions