to 中国龙:
你的sql语句我试了,如果只有25个项目的话应该是可以的,但是我现在的项目总数大于25个,使用你的语句得到的结果就不太正确了。或者是我使用的有问题?
...全文
6318打赏收藏
巨难:能动态的添加crossTab的列(用空白列填充)吗?或者使用sql语句?——中国龙请进来
我想将crossTab的列固定在25列,但是如果检索出来的项目小于25的话,怎么使用空白列来填充???? 我的项目总数是大于25的,但是通过条件检索后得到的项目数目应该是小于等于25的。 to 中国龙: 你的sql语句我试了,如果只有25个项目的话应该是可以的,但是我现在的项目总数大于25个,使用你的语句得到的结果就不太正确了。或者是我使用的有问题?
CREATE PROCEDURE usp_getCrossTab
@depaCode int = 15,
@courseTerm int = 2,
@studentGrade int = 2001,
@classAsAdmin varchar(5) = '02'
AS
SELECT VIEW_STUDENTGRADEINFO.studentCode,
studentInfoTab.studentName,
courseInfoTab.courseName,
VIEW_STUDENTGRADEINFO.credithour,
VIEW_STUDENTGRADEINFO.grade,
studentInfoTab.depaCode,
VIEW_STUDENTGRADEINFO.courseTerm,
studentInfoTab.studentGrade,
studentInfoTab.classasadmin
into #temp
FROM courseInfoTab,
studentInfoTab,
VIEW_STUDENTGRADEINFO
WHERE ( studentInfoTab.studentcode = VIEW_STUDENTGRADEINFO.studentCode ) AND
( VIEW_STUDENTGRADEINFO.courseCode = courseInfoTab.courseCode )
declare @courseCount int
declare @i int
declare @studentCode varChar(15)
declare @studentName varChar(50)
select @courseCount = count(distinct (courseName)) from #temp
/* where ( depaCode = @depaCode ) AND
( courseTerm = @courseTerm) AND
( studentGrade = @studentGrade ) AND
( classasadmin = @classAsAdmin )*/
select @studentCode = studentCode,@studentName = studentName from #temp
/* where ( depaCode = @depaCode ) AND
( courseTerm = @courseTerm) AND
( studentGrade = @studentGrade ) AND
( classasadmin = @classAsAdmin ) */
set @i = @courseCount
while ( @i < 25 ) begin
insert into #temp (studentCode,studentName,courseName,credithour,grade,depaCode,courseTerm,studentGrade,classAsAdmin)
values(@studentCode,@studentName,cast(@i as varchar(50)),0,'',@depaCode,@courseTerm,@studentGrade,@classAsAdmin)
set @i = @i + 1
end
select * from #temp
where ( depaCode = @depaCode ) AND
( courseTerm = @courseTerm) AND
( studentGrade = @studentGrade ) AND
( classasadmin = @classAsAdmin )