declare @sql varchar(8000)
set @sql ='select studentno,'
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' and TermNo='20082'
) f
print @sql
打印结果:
select studentno,
MAX(case courseno when '010301056' then Scores else NULL end) as '010301056',
MAX(case courseno when '010301093' then Scores else NULL end) as '010301093',
MAX(case courseno when '010301097' then Scores else NULL end) as '010301097',
把TermNo='20082'作为参数赋值给@termStr,下面的应该怎么改才正确?declare @sql varchar(8000)
declare @termStr varchar(50)
set @sql ='select studentno,'
set @termStr ='and TermNo=''20082'''
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' + @termStr
) f
print @sql
set @sql ='select studentno,'
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' and TermNo='20082'
) f
print @sql
打印结果:
select studentno,
MAX(case courseno when '010301056' then Scores else NULL end) as '010301056',
MAX(case courseno when '010301093' then Scores else NULL end) as '010301093',
MAX(case courseno when '010301097' then Scores else NULL end) as '010301097',
把TermNo='20082'作为参数赋值给@termStr,下面的应该怎么改才正确?declare @sql varchar(8000)
declare @termStr varchar(50)
set @sql ='select studentno,'
set @termStr ='and TermNo=''20082'''
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' + @termStr
) f
print @sql
set @sql ='select studentno'
select @sql = @sql
+',MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''''
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' and TermNo='20082'
) fset @sql = @sql + '
from ...
group by studentno
'
print @sql
exec(@sql)...部分自己填上
declare @sql varchar(8000)
set @sql ='select studentno'
select @sql = @sql
+',MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''''
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' and TermNo=@TermNo
) fset @sql = @sql + '
from ...
group by studentno
'
print @sql
exec(@sql)@TermNo参数的处理可以很简单
我的问题是在报表sql中,当传进来的学期@term为空时,就SELECT distinct CourseNo FROM vscoClassScore WHERE ClassNo = '0508YYB01'
当@term不为空时,就SELECT distinct CourseNo FROM vscoClassScore WHERE ClassNo = '0508YYB01' and term='20082'也就是
if<%TermNo%> = ''
set @termStr = ''
else
set @termStr = ' and termno=''<%TermNo%>'''现在把@termStr加到WHERE ClassNo = '0508YYB01' 后面试了半天老出错,想请教格式怎么写
LZ那里应该不能用@termStr这样的字符串连接,它并不是@sql的一部分,只能按2楼或4楼那种方式来写,不明白难道非得要那么写么?那不是弄复杂了。
select studentno,
MAX(case courseno when '010301056' then Scores else NULL end) as '010301056',
MAX(case courseno when '010301093' then Scores else NULL end) as '010301093',
MAX(case courseno when '010301097' then Scores else NULL end) as '010301097',
但无论怎么修改就是有问题,例如下面declare @sql varchar(8000)
declare @termStr varchar(50)
set @sql ='select studentno,'
set @termStr =' and TermNo=''20082'''
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = '0508YYB01' + @termStr
) f
print @sql--------------------
运行结果:select studentno,declare @sql varchar(8000)
declare @termStr varchar(50)
set @sql ='select studentno,'
set @termStr =' and TermNo=''20082'''
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''',' + '
from
(
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = ''0508YYB01''' + @termStr + '
) f'
print @sql--------------------
运行结果:消息 207,级别 16,状态 1,第 6 行
列名 'courseno' 无效。
消息 207,级别 16,状态 1,第 6 行
列名 'courseno' 无效。
那你可以将 20082 作为一个变量输进去
declare @str varchar(100)
set @str = '20082'后边用的时候直接 and TermNo = @str大致思路是这样! 等待高手!
当<%TermNo%>不为空时有值时,就 WHERE ClassNo = '0508YYB01' and TermNo=<%TermNo%>
create table #tmp(CourseNo varchar(12))declare @sql varchar(8000)
declare @selectSql varchar(2000)
declare @termStr varchar(50)
set @sql ='select studentno,'
set @termStr ='and TermNo=''20082'''
select @selectSql = '
insert into #tmp
SELECT distinct CourseNo
FROM vscoClassScore
WHERE ClassNo = ''0508YYB01''' + @termStr
exec(@selectSql)
select @sql = @sql
+'MAX(case courseno when ''' + courseno + ''' then Scores else NULL end) as ''' + courseno + ''','
from
#tmp
print @sql