CODEWITHSUNDEEP
sqlsql-server-2008
Kevin M.
Kevin M.

Reputation: 139

SQL Split Multiple Columns into Multiple Rows

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?

Upvotes: 5

Views: 3509

Answers (3)

Anthony Faull
Anthony Faull

Reputation: 17997

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

Upvotes: 4

Arnoud Kooi
Arnoud Kooi

Reputation: 1767

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

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;

Upvotes: 6

Related Questions