select count(*), EMP_ID FROM EMPHIST WHERE EMPHIST_DATE = '2012-01-01' GROUP BY EMP_ID HAVING COUNT(*)=2 order by 1
There are some entries of Employees listed in more than one office for a particular date.
EMPHIST can be joined OFFICE & LOCATION to get some other details..
we need a list of the duplicate in one row if possible..
example: Let's say John Adams in listed under Office A and also Office B as of Jan 1st 2012.. we want to report such cases.. with the duplicate info all in one row..
1st column: Office A's code (in Office table)
2nd Column: Office A's Description (in Office table)
3rd Column: County of Office A (in Office table)
4th Column: Location Number of Office A (in LOCATION table)
5th Column: Location Name of Office A(LOCATION table)
6th Column: Employee ID
7th column: Employee FirstName
8th column: Employee LastName
9st column: Office B's code (in Office table)
10th Column: Office B's Description (in Office table)
11th Column: County of Office B (in Office table)
12th Column: Location Number of Office B(in LOCATION table)
13th Column: Location Name of Office B(LOCATION table)
14th Column: Employee ID
15th column: Employee FirstName
16th column: Employee LastName
EmpHist and Office link through Office_key (PK-FK)
EmpHist.OFFICE_KEY = Office.OFFICE_KEY
office links to Location..
Office.Location_KEY = Location.Location_KEY
how would you merge these 2 sets of 8 columns into one row (16 columns) in tsql?