I have a table with employees and their respective managers in column 'emp' and 'manager' respectively. The numbers in the 'level' column represent different levels within an organisation for the employees in column 'emp'.

How do i write a query to get the manager name from the previous level as an entry in step and so forth.

CodePudding user response：

You seem to be looking for a recursive query. One that keeps joining on the next level until there are no more levels to join to.

That can be used to get all of an employees managers, each manager as a new row.

You then want to pivot those rows in to columns. Note, however, that SQL is statically and strongly typed, which means that if you want a pivoted view, you have to choose in advance how many columns you're going to have.

For example...

WITH
  recurse_upwards AS
(
  SELECT
    emp.emp_id,
    emp.emp          AS emp_name,
    0                AS level,
    mgr.emp_id       AS manager_id,
    mgr.emp          AS manager_name,
    mgr.manager_id   AS next_manager_id
  FROM
    example   AS emp
  LEFT JOIN
    example   AS mgr
      ON mgr.emp_id = emp.manager_id

  UNION ALL

  SELECT
    emp.emp_id,
    emp.emp_name,
    emp.level   1,
    mgr.emp_id,
    mgr.emp,
    mgr.manager_id
  FROM
    recurse_upwards   AS emp
  INNER JOIN
    example           AS mgr
      ON mgr.emp_id = emp.next_manager_id
)
SELECT
  emp_id,
  emp_name,
  MAX(CASE WHEN level = 0 THEN manager_id   END)  AS manager_id,
  MAX(CASE WHEN level = 0 THEN manager_name END)  AS manager_name,
  MAX(CASE WHEN level = 1 THEN manager_name END)  AS manager_1_name,
  MAX(CASE WHEN level = 2 THEN manager_name END)  AS manager_2_name,
  MAX(CASE WHEN level = 3 THEN manager_name END)  AS manager_3_name,
  MAX(CASE WHEN level = 4 THEN manager_name END)  AS manager_4_name
FROM
  recurse_upwards
GROUP BY
  emp_id,
  emp_name
ORDER BY
  emp_id

Demo : https://dbfiddle.uk/Tj7rZ5bT