请教一个问题，现在是原始的数据集
Staff_No Payroll_Code value
0001 AA 100
0001 BB 200
0001 CC 300
0002 AA 100
0002 BB 300
0002 CC 500
想得到这个效果
staff_no AA BB CC
0001 100 200 300
0002 100 300 500
请问应该怎么做，小弟感谢啦
------解决方案--------------------
- SQL code
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 @[email protected]+','+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][email protected]+' 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 allselect '0001','BB',200 union allselect '0001','CC',300 union allselect '0002','AA',100 union allselect '0002','BB',300 union allselect '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 3000002 100 300 500(2 行受影响)
------解决方案--------------------
（1）pivot法
- SQL code
select Staff_No,AA,BB,CCfrom(select * from #a) as dpivot(max(value) for Payroll_Code in ([AA],[BB],[CC])) as pvt