Stuck on how to write a query for data transformation

Question

I have a table with hierarchies in different rows which has data as follows:

StaffRefID	Name	DesignationRefID	Designation	ReportingToRefID
3497	Garrett R. Jacobson	1033	Officer	3012
3012	Ralph J. Thoma	1048	Manager	2371
2371	SDURGA RAO	1034	Senior manager	2269
2269	Patsy G. Pedrosa	1050	Chief Manager	2344
2344	Veronica J. Martin	1035	Assistant General Manager	2211
2211	Kelly P. Fitting	1039	General Manager	2301
3492	Willie M. Tucker	1033	Officer	3373
3373	Ruth E. Murray	1048	Manager	4265
4265	Benjamin B. Polk	1034	Senior manager	2344

Hierarchy goes like Officer --> Manager --> Senior manager --> Chief Manager --> Assistant General Manager --> General Manager.

But this is not always true. As you can see in the last row, Senior Manager directly reports to Assistant General Manager.

I want to convert these into columns, from lowest to higher. So my output would look like this.

Officer_ID	Officer_Name	Manager_ID	Manager_Name	Senior_Manager_ID	Senior_Manager_Name	Chief_Manager_ID	Cheif_Manager_Name	Assistant_General_Manager_ID	Assistant_General_Manager_Name	General_Manager_ID	General_Manager_Name
3497	Garrett R. Jacobson	3012	Ralph J. Thoma	2371	SDURGA RAO	2269	Patsy G. Pedrosa	2344	Veronica J. Martin	2211	Kelly P. Fitting
3492	Willie M. Tucker	3373	Ruth E. Murray	4265	Benjamin B. Polk			2344	Veronica J. Martin	2211	Kelly P. Fitting

What I have attempted:

SELECT
  a.StaffRefID as Officer_ID,
  a.Name as Officer_Name,
  b.StaffRefID as Manager_ID,
  b.Name as Manager_Name,
  c.StaffRefID as Senior_Manager_ID,
  c.Name as Senior_Manager_Name,
  d.StaffRefID as Chief_Manager_ID,
  d.Name as Cheif_Manager_Name,
  e.StaffRefID as Assistant_General_Manager_ID,
  e.Name as Assistant_General_Manager_Name,
  f.StaffRefID as General_Manager_ID,
  f.Name as General_Manager_Name
FROM
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
  WHERE
    DesignationRefID = '1033'
) as a
LEFT JOIN
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
) as b
ON
  a.ReportingToRefID = b.StaffRefID
LEFT JOIN
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
) as c
ON
  b.ReportingToRefID = c.StaffRefID
LEFT JOIN
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
) as d
ON
  c.ReportingToRefID = d.StaffRefID
LEFT JOIN
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
) as e
ON
  d.ReportingToRefID = e.StaffRefID
LEFT JOIN
(
  SELECT
    StaffRefID,
    Name,
    DesignationRefID,
    Designation,
    ReportingToRefID
  FROM
    `dummy_project.dummy_database.hierarchy_dummy`
) as f
ON
  e.ReportingToRefID = f.StaffRefID

Output I'm getting:

Officer_ID	Officer_Name	Manager_ID	Manager_Name	Senior_Manager_ID	Senior_Manager_Name	Chief_Manager_ID	Cheif_Manager_Name	Assistant_General_Manager_ID	Assistant_General_Manager_Name	General_Manager_ID	General_Manager_Name
3497	Garrett R. Jacobson	3012	Ralph J. Thoma	2371	SDURGA RAO	2269	Patsy G. Pedrosa	2344	Veronica J. Martin	2211	Kelly P. Fitting
3492	Willie M. Tucker	3373	Ruth E. Murray	4265	Benjamin B. Polk	2344	Veronica J. Martin	2211	Kelly P. Fitting

How can I accommodate for cases which don't always follow the usual hierarchy?

user_5221944 · Answer

Consider below approach

with temp as (
  select r1, r2, r3, r4, r5, r6
  from `dummy_project.dummy_database.hierarchy_dummy` r1 
  left join `dummy_project.dummy_database.hierarchy_dummy` r2 on r2.StaffRefID = r1.ReportingToRefID
  left join `dummy_project.dummy_database.hierarchy_dummy` r3 on r3.StaffRefID = r2.ReportingToRefID
  left join `dummy_project.dummy_database.hierarchy_dummy` r4 on r4.StaffRefID = r3.ReportingToRefID
  left join `dummy_project.dummy_database.hierarchy_dummy` r5 on r5.StaffRefID = r4.ReportingToRefID
  left join `dummy_project.dummy_database.hierarchy_dummy` r6 on r6.StaffRefID = r5.ReportingToRefID
  where r1.Designation = 'Officer'
)
select *  from (
  select r1.StaffRefID ID_Officer, r1.Name Name_Officer, r2.StaffRefID id, replace(r2.Designation, ' ', '_') Designation, r2.Name from temp union all
  select r1.StaffRefID, r1.Name, r3.StaffRefID, replace(r3.Designation, ' ', '_') Designation, r3.Name from temp union all
  select r1.StaffRefID, r1.Name, r4.StaffRefID, replace(r4.Designation, ' ', '_') Designation, r4.Name from temp union all
  select r1.StaffRefID, r1.Name, r5.StaffRefID, replace(r5.Designation, ' ', '_') Designation, r5.Name from temp union all
  select r1.StaffRefID, r1.Name, r6.StaffRefID, replace(r6.Designation, ' ', '_') Designation, r6.Name from temp 
)
pivot (min(id) ID, min(Name) Name for Designation in ('Manager', 'Senior_manager', 'Chief_Manager', 'Assistant_General_Manager', 'General_Manager'))

If applied to sample data in your question - output is

Stuck on how to write a query for data transformation

1 Answers