Some years ago, I had to create a pivot looking grid. I didn't know the word Pivot at the time, or how to do it, but I got some great help from a fellow developer, Per Holje.
And today I had to dig out the code once again, because of a project involving MS CRM 3.0 and Reporting Services.
The trick seems to be creating a scalar function that creates a dynamic SQL query, and the output looks like a pivot report. (OK, I might end up doing the actual report as a matrix report, but I thought I'd post this wanna-be-pivot report tip as well.)
Off course, SQL Server 2005 has a brand new Pivot function that works like a charm, but I'm stuck with SQL Server 2000 on this report.
The following should work against Northwind:
***
declare @CID int, @CName nvarchar(15), @Prod nvarchar(40), @tmp nvarchar(1000)
declare ccats cursor fast_forward for select CategoryID, CategoryName from Categories
declare @tab table (CategoryID int, CategoryName nvarchar(15), Products nvarchar(1000))
open ccats
fetch next from ccats into @CID, @CName
-- loop Categories
while @@fetch_status = 0 begin
set @tmp = ''
-- loop Products
declare cprods cursor fast_forward for
select ProductName from Products where CategoryID = @CID
open cprods; fetch next from cprods into @Prod
while @@fetch_status = 0 begin
set @tmp = @tmp + @Prod + ','
fetch next from cprods into @Prod
end
close cprods
deallocate cprods
-- clean string
if len(@tmp) > 0 set @tmp = left(@tmp, len(@tmp)-1)
-- store Category
insert into @tab values (@CID, @CName, @tmp)
fetch next from ccats into @CID, @CName
end
close ccats
deallocate ccats
select * from @tab
To make it into a scalar function, do this:
CREATE FUNCTION GetProds(@ProdName varchar(30))
RETURNS @ReturnTable
TABLE (CategoryID int, CategoryName nvarchar(15), Products nvarchar(1000))
AS
Begin
-> then the declare bit starts here, and the rest of the statement
and then in the end, remove "select * from @tab" and use this instead:
insert @ReturnTable
select * from @tab
return
end
go
And then you use it like this:
Select * from GetProds('Dairy')
***
Hope someone might use it.