Displaying columns as rows SQL-CodePudding

I have the below tables:

Corporate table:

CorporateId    DirectorId    ManagerId     SalesId

  1              1              1           1  
  2              2              2           3
  3              3              4           5

Employee table:

EmployeeId      FirstName    LastName

  1               Tim         Sarah           
  2               Tom         Paulsen           
  3               Tam         Margo
  4               Eli         Lot
  5               Ziva        Lit

I want to display, for one corporate,the names of the Director, Manager and Sales in rows. Example with corporate 3:

EmployeeId      FirstName    LastName

  3               Tam         Margo
  4               Eli         Lot
  5               Ziva        Lit

How can I do that? I know how to display rows as columns using pivot, but unsure if pivot can be used here also. Any help please?

CodePudding user response：

You can first un-pivot your rows into columns by using cross apply, after which you simply join the pivoted rows to your employee table:

select e.*
from corporate c
cross apply (
  select EmployeeId from (
    values (Directorid), (ManagerId), (SalesId)
  )r(EmployeeId)
)r
join employee e on e.EmployeeId = r.EmployeeId
where c.CorporateId = 3;

CodePudding user response：

You may join the two tables as the following:

SELECT E.EmployeeId, E.FirstName, E.LastName
FROM Employee E JOIN Corporate C
ON  E.EmployeeID IN (C.DirectorId ,C.ManagerId ,C.SalesId)
WHERE C.CorporateId=3

See a demo.