SQL查询将行转换为列 [英] SQL query to convert rows into columns

问题描述

我在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]，因为每个子级最多只能有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结合使用的数据透视和排名功能为您提供了每个员工人数所需的结果 首先，我们为每个分支中的每个员工分配一个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

这篇关于SQL查询将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！