Place Data from Same Column in Different Columns in Resultset

Question

I have a request I wasn't sure to handle. I was thinking of using PIVOT, but I wasn't sure if that would be the way to go.

I have the following Data:

EmployeeA, DepartmentB, 1/10/2010
EmployeeA, DepartmentA, 1/1/2000
EmployeeB, DepartmentC, 1/3/2011

They want output for only the employees that have been in different departments. Something that looks like this (order is important due to the dates):

EmployeeA, DepartmentA, DepartmentB

Any help is appreciated. For some reason, my mind isn't finding a good solution.

score 4 · Answer 1 · answered Aug 08 '12 at 22:09

4

You can do this by doing a self JOIN on the table and then using a PIVOT to get the data in the format that you want:

SELECT *
FROM 
(
    SELECT t1.emp, t1.dept, t1.dt
    FROM  test t1
    INNER JOIN test t2
        ON t1.emp = t2.emp
        AND t1.dept != t2.dept
) x
PIVOT
(
    min(dt)
    for dept in ([A], [B], [C], [D], [E])
) p

See SQL Fiddle with Demo

If you remove the JOIN you will get all records, but you stated you only want the records that have been in more than one department.

answered Aug 08 '12 at 22:09

Taryn

242,637
56
362
405

Works great. Only one issue. There can only be two "department" columns. Previous and current. – geoffrobinson Aug 09 '12 at 00:51
@geoffrobinson I am not sure what you mean only two departments. – Taryn Aug 09 '12 at 01:01
I got the answer largely from your help. I'll post in a second. – geoffrobinson Aug 09 '12 at 01:12

score 0 · Answer 2 · answered Aug 09 '12 at 01:15

Here's the answer I got which I got largely based on your work. Pivot doesn't work because I don't know the categories (in this case Department) ahead of time and I can only have two of them.

Maybe there's an easier way. I didn't use a CTE, because I believe this should work for Sybase as well which I don't think supports that.

   select Meta1.[Employee ID], 
Meta1.Department as PreviousDepartment,
Meta2.Department as CurrentDepartment
from
(
SELECT t1.[First Name], t1.[Last Name],
t1.[Employee ID], t1.Department, t1.[Hire Date],
ROW_NUMBER() over(PARTITION by t1.[EMPLOYEE ID] order by t1.[Hire Date]) as RowNum
FROM  EMPLOYEE t1
INNER JOIN EMPLOYEE t2
    ON t1.[Employee ID] = t2.[Employee ID]
    AND t1.Department != t2.Department
) Meta1  
inner join
(
SELECT t1.[Employee ID], t1.Department, t1.[Hire Date],
ROW_NUMBER() over(PARTITION by t1.[EMPLOYEE ID] order by t1.[Hire Date]) as RowNum
FROM  EMPLOYEE t1
INNER JOIN EMPLOYEE t2
    ON t1.[Employee ID] = t2.[Employee ID]
    AND t1.Department != t2.Department
) Meta2
on Meta1.[Employee ID]=Meta2.[Employee ID]
where Meta1.RowNum=1
and Meta2.RowNum=2

If you don't know the departments beforehand, a `PIVOT` can still work. There is a dynamic pivot that can be written see [this answer](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455) — Taryn, Aug 09 '12 at 01:20

Place Data from Same Column in Different Columns in Resultset

2 Answers2