We help IT Professionals succeed at work.

Create a pivot table with dynamic colums like in Excel

tusito
tusito asked
on
Hi experts,

I need to create a pivot table with SQL SERVER 2005, but I need create it with dynamic colums, because I don't know how many colums I will have.

I have this

Id      Service      Name
1      Service3      Jhon
2      Service3      Jim
3      Service3      Jim
4      Service3      Jim
5      Service3      Kim
6      Service3      Kim
7      Service3      Kim
8      Service2      Jhon
9      Service2      Jhon
10      Service2      Jim
11      Service2      Jim
12      Service2      Kim
13      Service1      Jhon
14      Service1      Jhon
15      Service1      Jhon
16      Service1      Jim
17      Service1      Kim
18      Service1      Kim
19      Service1      Kim

And I want this

Cuenta de Id      Rótulos de columna                  
Rótulos de fila      Service1      Service2      Service3      Total general
Jhon      3      2      1      6
Jim      1      2      3      6
Kim      3      1      3      7
Total general      7      5      7      19


Obviosly, I need a query that create a dynamic pivot table, with variable columns count.
Comment
Watch Question

Top Expert 2010
Commented: