Geek asks and answers

SQL Splitting multiple columns into multiple rows

I am having a problem with this problem.

I have a table with this structure:

OrderID | Manager   | Worker      
1       | John      | Sally       
2       | Tim       | Kristy

I need an SQL query to get a result set as follows:

OrderID | Employee       
1       | John           
1       | Sally    
2       | Tim 
2       | Kristy

Is it possible to accomplish this?

+5
source share
3 answers

The simplest way I can think of (unless you care if Tim is told before or after Christie):

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 the manager to always 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;
+6
source

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
+4
source

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
0
source

More articles:


All Articles