Pivot Table in Sql Server 2005 ( Part 2 )

Here my logic to make the field more fleksible. So you don't have to fix the field. Of course this method used when you need to make your report fleksible and don't need to add the new field if there's a new data inserted into master table.

declare @field varchar(max)
declare @select varchar(max)
declare @employeeid int

set @field = ''

declare cr cursor for
select employeeid
from HumanResources.Employee

open cr

fetch next from cr into @employeeid

while @@fetch_status = 0
begin
if @field <> ''
begin
set @field = @field + ','
end

set @field = @field + '[' + convert(varchar(max),@employeeid ) + ']'

fetch next from cr into @employeeid
end

close cr
deallocate cr

set @select =
'SELECT VendorID, ' + @field + '
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @field + ' )
) AS pvt ORDER BY VendorID;'

exec (@select)

Have a try. Ask if you still confuse.

Have a nice day.