挑战高难度，看看这个问题怎么解决。
我做了一个语句如下：
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 projectno

EXEC('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 
')