感谢网友TheGodOfGods (龙城飞将) 的帮助.
if object_id('tbData') is not null
drop table tbData
go
create table tbData([员工ID] int, [员工姓名] varchar(10),岗位工资 int,薪级工资 int,津贴 int,补贴 int,会费 int,年份 int,月份 int)
go
insert into tbData select 1,'张三',200,230,300,100,123,2010,3
union all select 1,'张三',200,230,300,100,123,2010,4
union all select 2,'李四',300,340,300,100,120,2010,3
union all select 2,'李四',300,340,300,100,120,2010,4
if object_id('tbProjects') is not null
drop table tbProjects
go
create table tbProjects([ID] int, 工资项目名称 varchar(20))
go
insert into tbProjects select 1,'岗位工资'
union all select 2,'薪级工资'
union all select 3,'津贴'
union all select 4,'补贴'
union all select 5,'会费'
go
select [员工ID],p.ID,金额,年份,月份 from tbData t
unpivot(金额 for 工资项目 in ([岗位工资],[薪级工资],[津贴],[补贴],[会费]))k
join tbProjects p on k.工资项目=p.工资项目名称
/* 员工ID ID 金额 年份 月份 ----------- ----------- ----------- ----------- -----------
1 1 200 2010 3
1 2 230 2010 3
1 3 300 2010 3
1 4 100 2010 3
1 5 123 2010 3
1 1 200 2010 4
1 2 230 2010 4
1 3 300 2010 4
1 4 100 2010 4
1 5 123 2010 4
2 1 300 2010 3
2 2 340 2010 3
2 3 300 2010 3
2 4 100 2010 3
2 5 120 2010 3
2 1 300 2010 4
2 2 340 2010 4
2 3 300 2010 4
2 4 100 2010 4
2 5 120 2010 4
*/