SQL query to convert rows into columns

Question

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

The columns can be fixed to [Mitarbeiter1] - [Mitarbeiter5], as there are not going to be more than 5 rows per Filiale.

Your help is greatly appreciated!

duplicate: http://stackoverflow.com/questions/1768586/sql-convert-rows-to-columns — manji, Jun 07 '11 at 15:31
I checked this out before posting my question but was not able to adapt the solution to solve my problem. — Dieter Gasser, Jun 07 '11 at 15:53

score 2 · Accepted Answer · answered Jun 07 '11 at 17:15

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 query to convert rows into columns

1 Answers1

Linked