Extracting data to a new model

Question

I have a table like the following:

I want to extract data like below. Please how can I do that?

Thanks !

Answer 1

You can use row_number() and group by:

select max(case when hierarchy = 'A' then l_name end) as A,
       max(case when hierarchy = 'B' then l_name end) as B,
       max(case when hierarchy = 'C' then l_name end) as C
from (select t.*,
             row_number() over (partition by hierarchy order by l_name) as seqnum
      from t
     ) t
group by seqnum;