SQL Split Multiple Columns into Multiple Rows

sql, sql-server-2008

Solution

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;

Problem

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?

Original source