-3

I have a table with employees and their location in two columns, I'm looking to only have one occurrence of the location and the employees listed in the columns to the left until there are not more employees at that location. The number of employees change from location to location.

Below is a sample of the table and then what I would like to see

name      location
MARILIS   ADAMS ST RECREATION CENTER
EDDIE     ADAMS ST RECREATION CENTER
CARMAN    DOWNTOWN OFFICE
DOROTHY   DOWNTOWN OFFICE
MICHAEL   DOWNTOWN OFFICE


location                     EMP1     EMP2    EMP3
ADAMS ST RECREATION CENTER   MARILIS  EDDIE
DOWNTOWN OFFICE              CARMAN   DOROTHY MICHAEL

I've tried to use crosstabs and it clearly doesn't work that way. If someone could help me out that would be fantastic.

Erik A
  • 31,639
  • 12
  • 42
  • 67
csar101
  • 1

1 Answers1

0

You can use a recursive CTE to number up the location partitions, and then PIVOT the data:

;WITH CTE AS (
SELECT NAME, LOCATION, 'EMP' + CAST(ROW_NUMBER() OVER(PARTITION BY LOCATION ORDER BY NAME ASC) AS VARCHAR) AS [EMP]
FROM TABLE1)
SELECT LOCATION, [EMP1], [EMP2], [EMP3]
FROM CTE
PIVOT(MAX(NAME) FOR [EMP] IN ([EMP1], [EMP2], [EMP3])) PIV
John Bell
  • 2,350
  • 1
  • 14
  • 23