dynamic calls to database

[dizzle]

ive been thinking about trying something new(or new to me) and im not sure how to go about doing it. i was wondering if anyone had suggestions. i basically have two tables

Teams
TID TName DivisionID
1 Hornets 2
2 Bulls 2
3 Cavs 2
4 Hawks 2

Players
PLID PName TID
1 Bob 1
2 Frank 1
3 Bill 2
4 Ralph 3

what i want to do is dynamically call the team names from the database so i can use them as column headings and also call the players dynamically and have them listed under the appropriate column names. i would assume that i need at least two record sets. but im not really sure, i basically know what i want, i just dont know how to get there. does anyone have any suggestions. please post if this is unclear, and maybe i can shed more light on the topic.

 

[nigelrivett]

This would be a crosstab query.
If you want to cope with an indeterminate number of teams and players (haven't tried to complile but should be something like this)

declare #a (dmy int)
declare #TID int ,
@sql varchar(4000) ,
@Team varchar(50)
select #TID = 0
while #TID < (select max(TID) from Teams)
begin
select @TID = min(TID) from Teams where TID > @TID)
select @Team = TName from Teams where TID = @TID
select @sql = 'alter table #TID add ' + @Team + ' varchar(50) null'
exec (@sql)
end

-- got all the teams as column heading now add all the players
-- all operations need to be in dynamic sql as the table structure is not accessible in this batch

declare @i int, , @maxi int
select @i = 0
while @i < (select max(cnt) from (select cnt = count(*) from Players group by TID) a)
begin
select @i = @i + 1
-- add a new row
select @sql = (insert #a (dmy) select ' + convert(varchar(20),@i)
exec (@sql)
select #TID = 0
while #TID < (select max(TID) from Teams)
begin
select @TID = min(TID) from Teams where TID > @TID)
select @Team = TName from Teams where TID = @TID
select @sql = 'update #TID set ' + @Team + ' = PName + ' from Players where PLID = (select top 1 PLID (from select top ' + convert(varchar(20),@i) + ' PLID from Players where TID = ' + convert(varchar(20),@TID) + ' order by PLID) a order by PLID desc) and dmy = ' + convert(varchar(20),@i)
exec (@sql)
end
end

select @sql = 'select * from #a order by dmy)
exec (@sql)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dizzle]

can you do a cross tab query in SQL Server?

 

[nigelrivett]

not in v2000 but you can code it yourself.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dizzle]

so i would essentially take that code and put it into the sql statement of my database connection on my asp page, or am i way off on how all this works?

 

[philhege]

Involved T-SQL code normally gets housed in stored procedures in SQL Server. You can then call the SP (and pass parameters) from your execute statement.

The nice thing about this approach is that you can parameterize the SP and make a crosstab out of dern near any table combination.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....