我在 SQL Server 2008 中有下表:
I have the following table in SQL Server 2008:
[ID] [Filiale] [Mitarbeiter]
1 01 Müller
2 01 Meier
3 01 Schmidt
4 02 Schulz
5 02 Schröder
我需要一个创建以下输出的查询:
I need a query which creates the following output:
[Filiale] [Mitarbeiter1] [Mitarbeiter2] [Mitarbeiter3] [Mitarbeiter4] [Mitarbeiter5]
01 Müller Meier Schmidt NULL NULL
02 Schulz Schröder NULL NULL NULL
列可以固定为 [Mitarbeiter1] - [Mitarbeiter5],因为每个 Filiale 不会超过 5 行.
The columns can be fixed to [Mitarbeiter1] - [Mitarbeiter5], as there are not going to be more than 5 rows per Filiale.
非常感谢您的帮助!
使用 SQL Server 2008,Pivot 和 Ranking 函数相结合,可为您提供每个员工数量所需的结果首先,我们为每个分支中的每个员工分配一个 ID,从每个新分支中的 1 开始,然后我们使用枢轴运算符来翻转结果
With SQL Server 2008 the Pivot and Ranking functions combined give you the desired result for each number of employees First we assign an ID to each empoyee in each branch starting with 1 in each new branch then we use the pivot operator to flip the result
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