SQLServer 2000: Denormalizing ?

Ever get one of those requests to denormalize a table into a view (sometimes referred to as pivot tables) ?

Here's some code to use a function and view in SQLServer to denormalize data from a single table to force a pivot table based on a key:

create table test_table
(
key_field int ,
cat_field varchar (512)
)

insert into test_table values (1,'John')
insert into test_table values (1,'R')
insert into test_table values (1,'Smith')
insert into test_table values (2,'John')
insert into test_table values (2,'C')
insert into test_table values (2,'Joe')

select * from test_table

key_field cat_field
---------- -------------------------------------------------------------
1 John
1 R
1 Smith
2 John
2 C
2 Joe

create function denorm (@key int)
returns varchar(4000)
as
BEGIN
declare
@mystr varchar(4000),
@tmpstr varchar(4000);

declare mystrcur cursor for
select cat_field
from test_table
WHERE key_field = @key;

open mystrcur;

fetch next from mystrcur into @tmpstr;

set @mystr = @tmpstr;

while @@fetch_status = 0
BEGIN
fetch next from mystrcur into @tmpstr;
if @@fetch_status = 0
set @mystr = @mystr + ',' + @tmpstr;
END

close mystrcur;
deallocate mystrcur;

return (@mystr);
end

create view test_view
as
select distinct key_field,dbo.denorm (key_field)as denorm_list
from test_table

select * from test_view

key_field denorm_list
----------- -----------------------------------------------------------------------
1 John,R,Smith
2 John,C,Joe