使用SQL Server
2008，将Pivot和Rank函数组合在一起，可以为每个雇员数量提供理想的结果。首先，我们为每个分支中的每个员工分配一个ID，从每个新分支中的1开始，然后使用透视运算符来翻转结果
create table data(id int, branch int, employee varchar(20)) insert into data (id, branch, employee) values (1, 1, 'M眉ller'), (2, 1, 'Meler'), (3, 1, 'Schmidt'), (4, 1, 'Schultz'), (5, 2, 'Schr枚der'), (6, 2, '=tg= Thomas'), (7, 3, 'Stephan')select branch, [1] as emp1, [2] as emp2, [3] as emp3, [4] as emp4, [5] emp5 from( select ROW_NUMBER() over (partition by branch order by id) employee_branch_id, branch, employee from data) data_with_employee_branch_id -- assign a number from 1 to n for each emplyee in the branch pivot ( max(employee) --it must be a aggregat, since we have only one row the max of a string will be the string for employee_branch_id in ( [1], [2], [3], [4], [5] )) as data_pvt
