How to use values from one table as column names in a new Table in SQL SERVER?-CodePudding

I have a table that looks like this:

EmployeeName	City
Maria	Chicago
John	Chicago
Anna	LA
Peter	LA
Carrie	NYC

And I need to create a table that will look like this:

Chicago	LA	NYC
Maria	Anna	Carrie
John	Peter

I found this

SELECT (ColumnNames)  
FROM (TableName)  
PIVOT
(  
 AggregateFunction(ColumnToBeAggregated)
  FOR PivotColumn IN (PivotColumnValues)
) AS (Alias);

But I am confused which aggregate function to use since the values are not numeric and also my table has too many Cities to write them manually.

CodePudding user response：

You can PIVOT with the addition of a new column. In this simple case I used the window function dense_rank() over()

Results

Declare @YourTable Table ([EmployeeName] varchar(50),[City] varchar(50))  Insert Into @YourTable Values 
 ('Maria','Chicago')
,('John','Chicago')
,('Anna','LA')
,('Peter','LA')
,('Carrie','NYC')
 

Select  *
 From  (
        Select EmployeeName
              ,City
              ,RN = dense_rank() over (partition by city order by EmployeeName desc) 
         From @YourTable
       ) src
 Pivot ( max(EmployeeName) for City in ( [Chicago],[LA],[NYC]) )pvt

Results

RN  Chicago LA      NYC
1   Maria   Peter   Carrie
2   John    Anna    NULL

UPDATE- Dynamic Approach

Declare @SQL varchar(max) = '
Select  *
 From  (
        Select EmployeeName
              ,City
              ,RN = dense_rank() over (partition by city order by EmployeeName desc) 
         From YourTable
       ) src
 Pivot ( max(EmployeeName) for City in ( '  (Select string_agg(quotename(City),',') From  (Select distinct City from YourTable) A)  ' ) )pvt
'
Exec(@SQL)