Displaying columns as rows SQL

Displaying columns as rows SQL

Problem Description:

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?

Solution – 1

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)
join employee e on e.EmployeeId = r.EmployeeId
where c.CorporateId = 3;

Solution – 2

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.

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.