请教SQL 列转行问题

[一段]

请教一个问题，现在是原始的数据集
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

请问应该怎么做，小弟感谢啦

[DBA_磊仔]

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

[發糞塗牆]

1楼的是静态的，我这是动态的。

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 行受影响)
 
 */

[發糞塗牆]

2楼的手误，这个才是对的，可以不管你有多少个payroll_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 Payroll_Code
 exec('select [Staff_No]'+@s+' from test group by [Staff_No]')
 /*
 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 行受影响)
 
 */
 
 */

[發糞塗牆]

妈的，结果贴错了......用这个

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 Payroll_Code
 exec('select [Staff_No]'+@s+' from test group by [Staff_No]')
 /*
 Staff_No   AA          BB          CC
 ---------- ----------- ----------- -----------
 0001       100         200         300
 0002       100         300         500
 
 (2 行受影响)
 
 */

[DBA_磊仔]

[gongjian0628]

http://blog.csdn.net/gongjian0628/article/details/7878066

[ttpsan550]

[Quote=引用 4 楼 的回复:]

妈的，结果贴错了......用这个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
……
[/Quote]

哈哈

[以学习为目的]

[Quote=引用 4 楼 的回复:]

妈的，结果贴错了......用这个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
……
[/Quote]黄老师，您慢点啊，怎么老是顾此失彼了

[-Tracy-McGrady-]

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 行受影响)

[onlykenny]

（1）pivot法

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

（2）case when 法

select Staff_NO, max(case Payroll_Code when 'AA' then value  end)as 'AA',
       max(case Payroll_Code when 'BB' then value   end) as 'BB',
       max(case Payroll_Code when 'CC' then value  end )as'CC' 
 from #a group by Staff_NO