高手请进，很复杂的纵表转横表问题

Question

不嫌麻烦的话，每个区间算一次，union all 20次

Answer 1

------------------------------------
-- Author: happyflystone 
-- Version:V1.001  
-- Date:2009-06-12  
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(学校 nvarchar(3),考号 int,成绩 int)
Go
Insert into ta
 select '学校A',1022,99 union all
 select '学校A',1023,98 union all
 select '学校B',1024,55 union all
 select '学校C',1025,88 
Go
--Start
Select  cast(ltrim(b.number * 5 )+'-'+ltrim((b.number+1)*5) as varchar) as '分数区间 ',
        (select count(1) from ta) '全区人数',
        *
into Tmp
from ta a
right join (select number from master..spt_values where type = 'P' and number <20) b
on a.成绩 between b.number * 5 + 1 and (b.number+1)*5declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+学校+'人数]= sum( case when 学校 = '''+学校+''' then 1 else  0 end),'+
'['+学校+'百分比]= cast(sum( case when 学校 = '''+学校+''' then 1 else  0 end)*1.000/全区人数 as numeric(12,2))'
from (select distinct 学校 from ta) a
exec('
select 分数区间,全区人数,全区百分比 = cast(sum(case when 考号 is null then 0 else 1 end)*1.00/全区人数 as numeric(12,2)),'+@s+' 
from tmp
group by 分数区间,全区人数
order by cast(left(分数区间,charindex(''-'',分数区间) -1) as int)')
drop table tmp
--Result:
/*
分数区间   全区人数   全区百分比   学校A人数  学校A百分比 学校B人数  学校B百分比  学校C人数   学校C百分比         
----- ----------- --------- ----------- ---------- ----------- -------------- ----------- ------- 
0-5                            4           .00            0           .00            0           .00            0           .00
5-10                           4           .00            0           .00            0           .00            0           .00
10-15                          4           .00            0           .00            0           .00            0           .00
15-20                          4           .00            0           .00            0           .00            0           .00
20-25                          4           .00            0           .00            0           .00            0           .00
25-30                          4           .00            0           .00            0           .00            0           .00
30-35                          4           .00            0           .00            0           .00            0           .00
35-40                          4           .00            0           .00            0           .00            0           .00
40-45                          4           .00            0           .00            0           .00            0           .00
45-50                          4           .00            0           .00            0           .00            0           .00
50-55                          4           .25            0           .00            1           .25            0           .00
55-60                          4           .00            0           .00            0           .00            0           .00
60-65                          4           .00            0           .00            0           .00            0           .00
65-70                          4           .00            0           .00            0           .00            0           .00
70-75                          4           .00            0           .00            0           .00            0           .00
75-80                          4           .00            0           .00            0           .00            0           .00
80-85                          4           .00            0           .00            0           .00            0           .00
85-90                          4           .25            0           .00            0           .00            1           .25
90-95                          4           .00            0           .00            0           .00            0           .00
95-100                         4           .50            2           .50            0           .00            0           .00*/
--End 

Answer 2

------------------------------------
-- Author: happyflystone 
-- Version:V1.001  
-- Date:2009-06-12 23:25:44
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(学校 nvarchar(3),考号 int,成绩 int)
Go
Insert into ta
 select '学校A',1022,99 union all
 select '学校a',1023,45 union all
 select '学校B',1024,55 union all
 select '学校C',1025,88 
Go
--Start
Select  cast(ltrim(b.number * 5 )+'-'+ltrim((b.number+1)*5) as varchar) as '分数区间 ',
        (select count(1) from ta) '全区人数',
        *
into Tmp
from ta a
right join (select number from master..spt_values where type = 'P' and number <20) b
on a.成绩 between b.number * 5 + 1 and (b.number+1)*5declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+学校+'人数]= sum( case when 学校 = '''+学校+''' then 1 else  0 end),'+
'['+学校+'百分比]= cast(cast(sum( case when 学校 = '''+学校+''' then 1 else  0 end)*100.000/全区人数 as numeric(12,2)) as varchar)+''%'''
from (select distinct 学校 from ta) a
exec('
select 分数区间,全区人数=sum(case when 考号 is null then 0 else 1 end)  ,全区百分比 = cast(cast(sum(case when 考号 is null then 0 else 1 end)*100.00/全区人数 as numeric(12,2)) as varchar)+''%'' ,'+@s+' 
from tmp
group by 分数区间,全区人数
order by cast(left(分数区间,charindex(''-'',分数区间) -1) as int)')
drop table tmp
--Result:
/*
分数区间  全区人数  全区百分比 学校A人数 学校A百分比 学校B人数 学校B百分比 学校C人数 学校C百分比      
----- ----------- -------- ----------- ----------- ----------- ------ ----------- ---------- 
0-5        0      0.00%       0      0.00%       0      0.00%       0      0.00%
5-10       0      0.00%       0      0.00%       0      0.00%       0      0.00%
10-15      0      0.00%       0      0.00%       0      0.00%       0      0.00%
15-20      0      0.00%       0      0.00%       0      0.00%       0      0.00%
20-25      0      0.00%       0      0.00%       0      0.00%       0      0.00%
25-30      0      0.00%       0      0.00%       0      0.00%       0      0.00%
30-35      0      0.00%       0      0.00%       0      0.00%       0      0.00%
35-40      0      0.00%       0      0.00%       0      0.00%       0      0.00%
40-45      1      25.00%      1      25.00%      0      0.00%       0      0.00%
45-50      0      0.00%       0      0.00%       0      0.00%       0      0.00%
50-55      1      25.00%      0      0.00%       1      25.00%      0      0.00%
55-60      0      0.00%       0      0.00%       0      0.00%       0      0.00%
60-65      0      0.00%       0      0.00%       0      0.00%       0      0.00%
65-70      0      0.00%       0      0.00%       0      0.00%       0      0.00%
70-75      0      0.00%       0      0.00%       0      0.00%       0      0.00%
75-80      0      0.00%       0      0.00%       0      0.00%       0      0.00%
80-85      0      0.00%       0      0.00%       0      0.00%       0      0.00%
85-90      1      25.00%      0      0.00%       0      0.00%       1      25.00%
90-95      0      0.00%       0      0.00%       0      0.00%       0      0.00%
95-100     1      25.00%      1      25.00%      0      0.00%       0      0.00%*/
--End 

Answer 3

---盗版的  试下新的签名 感觉真的不错 谢谢石头哥 
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-06-12 23:53:42
----------------------------------------------------------------
--> 测试数据：@tb
declare @tb table([学校] varchar(5),[考号] int,[成绩] int)
insert @tb
select '学校A',1022,99 union all
select '学校A',1023,45 union all
select '学校B',1024,55 union all
select '学校C',1025,88
Select  cast(ltrim(b.number * 5 )+'-'+ltrim((b.number+1)*5) as varchar) as '分数区间 ',
        (select count(1) from @tb) '全区人数',
        *
into Tmp
from @tb a
right join (select number from master..spt_values where type = 'P' and number <20) b
on a.成绩 between b.number * 5 + 1 and (b.number+1)*5
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+学校+'人数]= sum( case when 学校 = '''+学校+''' then 1 else  0 end),'+
'['+学校+'百分比]= cast(sum( case when 学校 = '''+学校+''' then 1 else  0 end)*1.000/全区人数 as numeric(12,2))'
from (select distinct 学校 from @tb) a
exec('
select 分数区间,全区人数,全区百分比 = cast(sum(case when 考号 is null then 0 else 1 end)*1.00/全区人数 as numeric(12,2)),'+@s+' 
from tmp
group by 分数区间,全区人数
order by cast(left(分数区间,charindex(''-'',分数区间) -1) as int)')
drop table tmp
--Result:
/*
分数区间   全区人数   全区百分比   学校A人数  学校A百分比 学校B人数  学校B百分比  学校C人数   学校C百分比         
----- ----------- --------- ----------- ---------- ----------- -------------- ----------- ------- 
0-5                            4           .00            0           .00            0           .00            0           .00
5-10                           4           .00            0           .00            0           .00            0           .00
10-15                          4           .00            0           .00            0           .00            0           .00
15-20                          4           .00            0           .00            0           .00            0           .00
20-25                          4           .00            0           .00            0           .00            0           .00
25-30                          4           .00            0           .00            0           .00            0           .00
30-35                          4           .00            0           .00            0           .00            0           .00
35-40                          4           .00            0           .00            0           .00            0           .00
40-45                          4           .00            0           .00            0           .00            0           .00
45-50                          4           .00            0           .00            0           .00            0           .00
50-55                          4           .25            0           .00            1           .25            0           .00
55-60                          4           .00            0           .00            0           .00            0           .00
60-65                          4           .00            0           .00            0           .00            0           .00
65-70                          4           .00            0           .00            0           .00            0           .00
70-75                          4           .00            0           .00            0           .00            0           .00
75-80                          4           .00            0           .00            0           .00            0           .00
80-85                          4           .00            0           .00            0           .00            0           .00
85-90                          4           .25            0           .00            0           .00            1           .25
90-95                          4           .00            0           .00            0           .00            0           .00
95-100                         4           .50            2           .50            0           .00            0           .00*/
--End 

Answer 4

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-06-12 23:53:42
----------------------------------------------------------------
--> 测试数据：@tb
declare @tb table([学校] varchar(5),[考号] int,[成绩] int)
insert @tb
select '学校A',1022,99 union all
select '学校A',1023,45 union all
select '学校B',1024,55 union all
select '学校C',1025,88Select  cast(ltrim(b.number * 5 )+'-'+ltrim((b.number+1)*5) as varchar) as '分数区间 ',
        (select count(1) from @tb) '全区人数',
        *
into Tmp
from @tb a
right join (select number from master..spt_values where type = 'P' and number <20) b
on a.成绩 between b.number * 5 + 1 and (b.number+1)*5declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+学校+'人数]= sum( case when 学校 = '''+学校+''' then 1 else  0 end),'+
'['+学校+'百分比]= cast(cast(sum( case when 学校 = '''+学校+''' then 1 else  0 end)*100.000/全区人数 as numeric(12,2)) as varchar)+''%'''
from (select distinct 学校 from @tb) a
exec('
select 分数区间,全区人数=sum(case when 考号 is null then 0 else 1 end)  ,全区百分比 = cast(cast(sum(case when 考号 is null then 0 else 1 end)*100.00/全区人数 as numeric(12,2)) as varchar)+''%'' ,'+@s+' 
from tmp
group by 分数区间,全区人数
order by cast(left(分数区间,charindex(''-'',分数区间) -1) as int)')
drop table tmp
/*
分数区间  全区人数  全区百分比 学校A人数 学校A百分比 学校B人数 学校B百分比 学校C人数 学校C百分比      
----- ----------- -------- ----------- ----------- ----------- ------ ----------- ---------- 
0-5        0      0.00%       0      0.00%       0      0.00%       0      0.00%
5-10       0      0.00%       0      0.00%       0      0.00%       0      0.00%
10-15      0      0.00%       0      0.00%       0      0.00%       0      0.00%
15-20      0      0.00%       0      0.00%       0      0.00%       0      0.00%
20-25      0      0.00%       0      0.00%       0      0.00%       0      0.00%
25-30      0      0.00%       0      0.00%       0      0.00%       0      0.00%
30-35      0      0.00%       0      0.00%       0      0.00%       0      0.00%
35-40      0      0.00%       0      0.00%       0      0.00%       0      0.00%
40-45      1      25.00%      1      25.00%      0      0.00%       0      0.00%
45-50      0      0.00%       0      0.00%       0      0.00%       0      0.00%
50-55      1      25.00%      0      0.00%       1      25.00%      0      0.00%
55-60      0      0.00%       0      0.00%       0      0.00%       0      0.00%
60-65      0      0.00%       0      0.00%       0      0.00%       0      0.00%
65-70      0      0.00%       0      0.00%       0      0.00%       0      0.00%
70-75      0      0.00%       0      0.00%       0      0.00%       0      0.00%
75-80      0      0.00%       0      0.00%       0      0.00%       0      0.00%
80-85      0      0.00%       0      0.00%       0      0.00%       0      0.00%
85-90      1      25.00%      0      0.00%       0      0.00%       1      25.00%
90-95      0      0.00%       0      0.00%       0      0.00%       0      0.00%
95-100     1      25.00%      1      25.00%      0      0.00%       0      0.00%*/

Answer 5

------------------------------------
-- Author: happyflystone 
-- Version:V1.001  
-- Date:2009-06-12  
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(学校 nvarchar(3),考号 int,成绩 int)
Go
Insert into ta
 select '学校A',1022,99 union all
 select '学校A',1023,98 union all
 select '学校B',1024,55 union all
 select '学校C',1025,88 
Go
--Start
Select  cast(ltrim(b.number * 5 )+'-'+ltrim((b.number+1)*5) as varchar) as '分数区间 ',
        (select count(1) from ta) '全区人数',
        *
into Tmp
from ta a
right join (select number from master..spt_values where type = 'P' and number <20) b
on a.成绩 between b.number * 5 + 1 and (b.number+1)*5declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+学校+'人数]= sum( case when 学校 = '''+学校+''' then 1 else  0 end),'+
'['+学校+'百分比]= cast(sum( case when 学校 = '''+学校+''' then 1 else  0 end)*1.000/全区人数 as numeric(12,2))'
from (select distinct 学校 from ta) a
exec('
select 分数区间,全区人数,全区百分比 = cast(sum(case when 考号 is null then 0 else 1 end)*1.00/全区人数 as numeric(12,2)),'+@s+' 
from tmp
group by 分数区间,全区人数
order by cast(left(分数区间,charindex(''-'',分数区间) -1) as int)')
drop table tmp
--Result:
/*
分数区间   全区人数   全区百分比   学校A人数  学校A百分比 学校B人数  学校B百分比  学校C人数   学校C百分比         
----- ----------- --------- ----------- ---------- ----------- -------------- ----------- ------- 
0-5                            4           .00            0           .00            0           .00            0           .00
5-10                           4           .00            0           .00            0           .00            0           .00
10-15                          4           .00            0           .00            0           .00            0           .00
15-20                          4           .00            0           .00            0           .00            0           .00
20-25                          4           .00            0           .00            0           .00            0           .00
25-30                          4           .00            0           .00            0           .00            0           .00
30-35                          4           .00            0           .00            0           .00            0           .00
35-40                          4           .00            0           .00            0           .00            0           .00
40-45                          4           .00            0           .00            0           .00            0           .00
45-50                          4           .00            0           .00            0           .00            0           .00
50-55                          4           .25            0           .00            1           .25            0           .00
55-60                          4           .00            0           .00            0           .00            0           .00
60-65                          4           .00            0           .00            0           .00            0           .00
65-70                          4           .00            0           .00            0           .00            0           .00
70-75                          4           .00            0           .00            0           .00            0           .00
75-80                          4           .00            0           .00            0           .00            0           .00
80-85                          4           .00            0           .00            0           .00            0           .00
85-90                          4           .25            0           .00            0           .00            1           .25
90-95                          4           .00            0           .00            0           .00            0           .00
95-100                         4           .50            2           .50            0           .00            0           .00*/
--End 引用一下。

Answer 6

--SQL 2005可以用
declare @s varchar(max)

Answer 7

1. SQL 2005 有 nvarchar(max)
2. SQL 2000 要用多个子串。例如
   declare @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(8000)
   --分别赋值
    EXEC(@s1+@s2+@s3)