select * from 表 pivot(sum(value) for Payroll in([AA],[BB],[CC]))p

------解决方案--------------------

1楼的是静态的，我这是动态的。
SQL code
CREATE TABLE  test (Staff_No VARCHAR (10), Payroll_Code VARCHAR (10),VALUE INT )
 
 INSERT INTO test 
 SELECT  '0001' ,'AA', 100
 UNION ALL 
 SELECT   '0001' , 'BB' ,200
  UNION ALL 
 SELECT   '0001' , 'CC' ,300
  UNION ALL 
 SELECT   '0002', 'AA' ,100
  UNION ALL 
  SELECT  '0002',  'BB', 300
  UNION ALL 
 SELECT   '0002',  'CC' ,500
 
 declare @s nvarchar(4000)
 set @s=''
 Select     @s=@s+','+quotename(Payroll_Code)+'=max(case when [Payroll_Code]='+quotename(Payroll_Code,'''')+' then [VALUE] else 0 end)'
 from test group BY [Staff_No],Payroll_Code
 exec('select [Staff_No]'+@s+' from test group by [Staff_No]')
 /*
 Staff_No   AA          BB          CC          AA          BB          CC
 ---------- ----------- ----------- ----------- ----------- ----------- -----------
 0001       100         200         300         100         200         300
 0002       100         300         500         100         300         500
 
 (2 行受影响)
 
 */

------解决方案--------------------
SQL code

declare @table table(Staff_No varchar(10),Payroll_Code varchar(10),value1 int)
insert into @table 
select '0001','AA',100 union all
select '0001','BB',200 union all
select '0001','CC',300 union all
select '0002','AA',100 union all
select '0002','BB',300 union all
select '0002','CC',500
--语句
select Staff_No,
AA=max(case when Payroll_Code='AA' then value1 else 0 end),
BB=max(case when Payroll_Code='BB' then value1 else 0 end),
CC=max(case when Payroll_Code='CC' then value1 else 0 end)
from @table group by Staff_No

--结果
Staff_No   AA          BB          CC
---------- ----------- ----------- -----------
0001       100         200         300
0002       100         300         500

(2 行受影响)

------解决方案--------------------
（1）pivot法
SQL code

select Staff_No,AA,BB,CC
from
(select * from #a) as d
pivot(max(value) for Payroll_Code in ([AA],[BB],[CC])) as pvt