挑战高难度，看看这个有关问题如何解决

挑战高难度，看看这个问题怎么解决。
我做了一个语句如下：
select department,test_method,projectno,  
  sum(case projectno when 'JLZ9100501' then total_num else 0 end) JLZ9100501t,
  sum(case projectno when 'JLZ9100501' then total_num-rejected_num else 0 end) JLZ9100501p,
  sum(case projectno when 'JLZ9100502' then total_num else 0 end) JLZ9100502t,
  sum(case projectno when 'JLZ9100502' then total_num-rejected_num else 0 end) JLZ9100502p
from tbl_ndtproposor where enter_area='NJ' and (sflag='1' and pflag='1' or sflag='2' and pflag='1') and disp='0' 
group by department,test_method,projectno order by department, projectno 

其中'JLZ9100501'与'JLZ9100502'是工程号，我做了一个一个行转列，但是我数据表里的工程号很多，需要动态的写法，请问有大侠能解答吗。


[解决办法]

SQL code

DECLARE @s NVARCHAR(4000)SET @s=''SELECT @s=@s+',['+projectno+'t]=sum(case when projectno='''+projectno+''' then total_num else 0 end),['            +projectno+'p]=sum(case when projectno='''+projectno+''' then total_num-rejected_num else 0 end)'FROM tbl_ndtproposor GROUP BY projectnoEXEC('select department,test_method,projectno'+@s+' from tbl_ndtproposor where enter_area=''NJ'' and (sflag=''1'' and pflag=''1'' or sflag=''2'' and pflag=''1'') and disp=''0''  group by department,test_method,projectno order by department, projectno ')