SQL问题(在线等待)(50分)

在SQL2000数据库,这样的SQL怎么实现 :<br>FMaterialCode &nbsp;FQuantity FAccountTerm<br>01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp;1<br>02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp;1<br>03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp;1<br>01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp;2<br>04 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80 &nbsp; &nbsp; &nbsp; &nbsp;2<br>....<br><br>最终实现形式:<br>FMaterialCode &nbsp;FAccountTerm|1 &nbsp;FAccountTerm|2 &nbsp;FAccountTerm...<br>01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nil<br>02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nil &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; nil <br>03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nil &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; nil <br>04 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; nil &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nil &nbsp; <br>....

下面说一下交叉表的概念<br>说到交叉表先提一下递归的select变量<br>递归的select变量可以使用select语句和子查询将一个变量与其自身拼接起来。<br>举一个例子<br>select @var=@var +d.column from table1 a<br>从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。<br>下面就是动态交叉表和静态的交叉表的一个比较，动态的交叉表这样就代替了传统的游标。<br>交叉表<br>方法1<br>select &nbsp;f_nUMBER as '学员', <br>SUM(case f_subject when 'A01' then f_nUM end) as 'A01',<br>SUM(case f_subject when 'A02' then f_nUM end) as 'A02' ,<br>SUM(case f_subject when 'A03' then f_nUM end) as 'A03' ,<br>SUM(case f_subject when 'A04' then f_nUM end) as 'A04' ,<br>SUM(case f_subject when 'A05' then f_nUM end) as 'A05' ,<br>SUM(case f_subject when 'A06' then f_nUM end) as 'A06' ,<br>SUM(case f_subject when 'A07' then f_nUM end) as 'A07' ,<br>SUM(case f_subject when 'A08' then f_nUM end) as 'A08' ,<br><br>SUM(case f_subject when 'A09' then f_nUM end) as 'A09' <br>from rowdata group by f_nUMBER order by f_nUMBER<br><br>&nbsp;<br><br>方法2<br>declare @sql nvarchar(2000)<br>set @sql=''<br>select @sql=@sql+ 'sum(case F_subject when '''+ a.F_subject +''' then F_Num else 0 end) as '<br>+a.F_Name+','<br>from (select &nbsp;distinct top 100 percent F_subject,F_Name from rowdata b JOIN SUBJECT_name c on b.F_subject=c.F_number order by F_subject ) a<br>set @sql='select f_nUMBER as '+'"学员",'+@sql + 'count(F_Number) as '+'"考试数目"'+ <br>'from rowdata group by F_Number order by F_Number'<br>print @sql<br>exec sp_executesql @sql