I'm having difficulty with this problem.
I have a table with this structure:
OrderID | Manager | Worker
1 | John | Sally
2 | Tim | Kristy
I need a SQL query to get a result set like this:
OrderID | Employee
1 | John
1 | Sally
2 | Tim
2 | Kristy
Is this possible to perform?
Simplest way I can think of is (assuming you don't care if Tim is listed before or after Kristy):
SELECT OrderID, Employee = Manager FROM dbo.table
UNION ALL
SELECT OrderID, Employee = Worker FROM dbo.table
ORDER BY OrderID;
If order matters, and you want manager first always, then:
SELECT OrderID, Employee FROM
(
SELECT r = 1, OrderID, Employee = Manager
FROM dbo.Table
UNION ALL
SELECT r = 2, OrderID, Employee = Worker
FROM dbo.table
) AS x
ORDER BY OrderID, r;
You can use UNPIVOT for this.
SELECT p.OrderID, p.Employee
FROM (SELECT OrderID, Manager, Worker FROM table) a
UNPIVOT (Employee FOR FieldName IN (Manager, Worker)) p
Try something like
SELECT OrderID, Manager AS Employee, 'Manager' AS EmployeeRole From Employess
UNION ALL
SELECT OrderID, Worker AS Employee, 'Worker' AS EmployeeRole From Employess
