How to get category of sub items defined as a separate row in the same table

Question

I have a table in SQL Server 2017 like below:

Type Description
H    General Income
H    Property Income
R    Rent
R    Parking
R    Storage
H    Cash Flow 
H    Other Income
R    Specials

Here, H refers to Header and R refers to Role. The roles belong to the header defined above them. For example, General Income has no role defined yet but Property Income has three roles under it - rent, parking and storage.

My expected output is:

Header             RoleDescription
General Income    
Property Income    Rent
Property Income    Parking
Property Income    Storage
Cash Flow
Other Income       Specials

How can I get this, I couldn't find it. Any help would be appreciated.

Edit: I have an ID column, yes, I believe I can use it for ordering.

Answer 1

Assuming your table has an ordering column, you can approach this by using a cumulative conditional sum to define the groups.

Then there is a trick. Use conditional aggregation for the role and conditional aggregation with a window function for the header:

select max(max(case when type = 'H' then description end)) over (partition by grp) as header,
       max(case when type = 'R' then description end) as role
from (select t.*,
             row_number() over (partition by type, grp order by <ordering col) as seqnum
      from (select t.*,
                   sum(case when type = 'H' then 1 else 0 end) over (order by <ordering col>) as grp
            from t
           ) t
     ) t
group by seqnum
order by grp, seqnum;