SQL Split Multiple Columns into Multiple Rows

Question

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?

Answer 1

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;

Answer 2

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