Rolling up rows and creating a new row per roll up

February 15, 2023

I have a table like this:

DepartmentName | SubDivisionName | Importance
Security       | Cyber           | 1
Security       | Airlines        | 2
Security       | Banks           | 3
Health         | Children        | 4
Health         | Elderly         | 5
Housing        | Housing         | 6
Misc           |                 | 7

I want to create a new table ( a temp table ) based off of that to look like this below. Notice that whenever we GROUP BY DepartmentName and number of its GROUP members is bigger than one, it means that DepartmentName has some SubDivisions so we want to insert a new row for **that **and Importance value would get updated accordingly.


DepartmentName | SubDivisionName | Importance
Security       |                 | 1
Security       | Cyber           | 2
Security       | Airlines        | 3
Security       | Banks           | 4
Health         |                 | 5
Health         | Children        | 6
Health         | Elderly         | 7
Housing        | Housing         | 8
Misc           |                 | 9

I tried some GROUP BY to find the ones that have more than one record but still had trouble inserting new rows and correctly updating the importance column.

>Solution :

Does the following provide your expected results?

You can union your existing data with rows that have >1 DepartmentNames and then use row_number to provide the new sequence:

with u as (
    select DepartmentName, SubDivisionName, Importance 
    from t
    union all
    select DepartmentName, null, Min(Importance)
    from t
    group by DepartmentName
    having Count(*) > 1
)
select DepartmentName, SubDivisionName, 
  Row_Number() over(order by Importance, SubDivisionName) as Importance
from u
order by Importance;

Demo Fiddle