续之前的帖子，动态查询部分已经实现但是如何加入行转列。。

wofel 2011-10-25 08:53:11



DECLARE @Max INT,@Min INT,@Str NVARCHAR(3000)

select @MAX= max(三门), @MIN= MIN(三门) from zongchenjibiao WHERE 语文缺='0' and 数学缺='0' and 英语缺='0' 

SELECT @Max=@Max/10*10,@Min=@Min/10*10,@str=N' Case '

WHILE @Min<=@Max

    BEGIN

        SELECT @str=@str+N' WHEN 三门 BETWEEN '+RTRIM(@MIN)+' AND '+rtrim(@MIN+9)+' THEN '''+RTRIM(@MIN)+'-'+rtrim(@MIN+9)+'''',@MIN=@MIN+10

    END



EXEC ( 'select [分数段]='+@Str+' else ''不在范围'' end,'+

'sum(case when  zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''01'' then 1 else 0 end) as [高二01],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''02'' then 1 else 0 end) as [高二02],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''03'' then 1 else 0 end) as [高二03],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''04'' then 1 else 0 end) as [高二04],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''05'' then 1 else 0 end) as [高二05],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''06'' then 1 else 0 end) as [高二06],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''07'' then 1 else 0 end) as [高二07],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''08'' then 1 else 0 end) as [高二08],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''09'' then 1 else 0 end) as [高二09],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''10'' then 1 else 0 end) as [高二10],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''11'' then 1 else 0 end) as [高二11],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''12'' then 1 else 0 end) as [高二12],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''13'' then 1 else 0 end) as [高二13],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''14'' then 1 else 0 end) as [高二14],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''15'' then 1 else 0 end) as [高二15],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''16'' then 1 else 0 end) as [高二16],

    sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''17'' then 1 else 0 end) as [高二17]

	---into linshis

    from dangqianbanji,zongchenjibiao

	

    where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺=0 and 数学缺=0 and 英语缺=0

    group by  '+@str+' else ''不在范围'' end')

能看到， ‘’sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''17'' then 1 else 0 end) as [高二17]‘’ 这句话重复了很多遍，事实上很多时候不是一个年级所有的班都参与排名，所以会导致不想要的结果出现。。。。
不参与排名的班级肯定不会在‘zongchenjibiao’中出现

所以有个”行转列“的方法



declare @sql varchar(8000)

---select @sql= '高一['+min(班级)+']' from zongchenjibiao WHERE 语文缺='0' and 数学缺='0' and 英语缺='0' 

---select @sql

select @sql =isnull(@sql + ',' , '')  + 班级全称 from zongchenjibiao group by 班级全称

select @sql

exec ('select * 

---into linshih 

from (select 班级全称,三门 from zongchenjibiao) a pivot (max(三门) for 班级全称 in (' + @sql + ')) b')

用上面的方法可以找出出现过的班级，而不是直接死的把所有班级都罗列出来。

所以现在问题来了，如何把这2个查询结合起来呢。。。2个都是动态查询。。。似乎很复杂

附第一个查询结果(部分)
分数段高一01 高一02 高一03.。。。。。。。。。。。。
310-319 1 0 0 0 0 0
320-329 1 0 0 0 0 0
350-359 0 2 0 0 0 0
360-369 3 1 0 0 0 0
370-379 3 1 0 0 0 0
380-389 1 0 0 0 0 0
390-399 0 1 0 0 0 0

第二个查询结果
高一01 高一02
383 393

zongchenjibiao有数据
学号，年级，班级，班级全称（可删），三门。。。
里面有参与考试需要排名的所有考生的成绩

...全文