我做了一个语句如下：
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 @[email protected]+',['+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,[email protected]+' 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 ')