Help in TSQL crosstable query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help in TSQL crosstable query

Author

Topic

ramta
Starting Member

2 Posts

Posted - 2005-04-24 : 15:44:55

I have attendence table that has data like:

StudentName Subject Date
-----------------------------
ABC English 1/1/2005
ABC Math 1/1/2005
BCD English 1/1/2005
ABC English 2/1/2005
BCD English 2/1/2005
BCD Math 2/1/2005

I like to generate results in following format using ASP webpage:

Name 1/1/2005 2/1/2005 .....
--------------------------------------
ABC 2 1
BCD 1 2

Please help me in getting this result in this format.

Which technique will give better performance:
1. SQL Crosstable query
2. Processing data in ASP to generate above format.

Thanks in advance

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-24 : 16:00:44

one way in t-sql:

set nocount on

declare @attendance table (student_name varchar(15), subject varchar(15), attendance_date datetime)
insert into @attendance 	
	select 'ABC','English','1-1-2005' union 
	select 'ABC','Math','1-1-2005' union
	select 'BCD','English','1-1-2005' union
	select 'ABC','English','2-1-2005' union
	select 'BCD','English','2-1-2005' union
	select 'BCD','Math','2-1-2005'


-- select * from @attendance

select 
	student_name,
	SUM(CASE WHEN attendance_date = '1-1-2005' THEN 1 ELSE 0 END) AS '1-1-2005',
	SUM(CASE WHEN attendance_date = '2-1-2005' THEN 1 ELSE 0 END) AS '2-1-2005'
from @attendance
group by student_name

ramta
Starting Member

2 Posts

Posted - 2005-04-25 : 03:32:19

Thanks for your solution.

The problem is that the range for Date column is not fixed. The query may be for a week, month or even a year.

The query will be passed two dates FROMDATE and TODATE parameters. The query will than generate the format

Name FROMDATE FROMDATE+1 FROMDATE+2 ... TODATE
ABC 2 1 4 6
BCD 1 2 3 5

Thanks

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-25 : 20:17:36

I think you are going to be stuck with dynamic SQL if these results must be as columns based on date range input... I feel like this query is becoming too complicated for such an easy resultset :)

Perhaps take a look at the db design as well, but here is example:

set nocount on

create table #attendance (student_name varchar(15), subject varchar(15), attendance_date datetime)
insert into #attendance 	
	select 'ABC','English','1-1-2005' union 
	select 'ABC','Math','1-1-2005' union
	select 'BCD','English','1-1-2005' union
	select 'ABC','English','1-2-2005' union
	select 'BCD','English','1-3-2005' union
	select 'ABC','Math','1-4-2005' union
	select 'BCD','English','1-4-2005' union
	select 'ABC','English','1-5-2005' union
	select 'BCD','English','1-5-2005' 


-- select * from @attendance
declare @start_date datetime,
	@end_date datetime,
	@cur_date datetime,
	@cmd varchar(8000)

select 	@start_date = '1-1-2005',
	@end_date = '1-5-2005' 

select 	@cur_date = @start_date,
	@cmd = 'SELECT student_name, '

while @cur_date < @end_date
begin
	select @cmd = @cmd + 'SUM(CASE WHEN attendance_date = ''' + CONVERT(VARCHAR,@cur_date) + ''' THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR,@cur_date) + ''','
	select @cur_date = (@cur_date + 1)
end
select @cmd = substring(@cmd,1,len(@cmd)-1) + ' from #attendance group by student_name'
exec (@cmd)
--print @cmd

drop table #attendance

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-26 : 07:54:30

The cross-tab query available on this website is a good solution but you have to be careful that the generated CASE statement (see the cross-tab stored procedure) does not exceed 8000 characters. I have a feeling that the stored procedure will not work for you as your variable column is the attendance and I presume that students will have over 100 dates.

You might be better off performing this in your ASP layer.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-26 : 17:10:11

Yes, I should have noted that we are limited here with varchar(8000).

Good looking out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources