需要多种方法去实现.希望大家给点建议.最好能给小弟2种方法

Question

有两张表,表1结构为"身份证号","姓名","编号", 表2结构为"姓名","身份证号","岗位工资","效益工资","工龄工资","房帖" 现在要生成一表,结构为"编号","姓名","非固定工资项目","金额". 
例表1 
------------------------ 
身份证号 姓名 编号 
111111 张三 001.001 
111112 李四 001.002 
111113 王五 001.003 表二 
----------------------------- 
姓名 身份证号 岗位工资 效益工资 工龄工资 房帖 
张三 111111 4300 2000 1000 500 
李四 111112 3200 1800 800 450 
王五 111113 3000 1600 700 400 求表结构为 
------------------------------------------------- 
编号 姓名 非固定工资项目 金额 
001.001 张三 岗位工资 4300 
001.001 张三 效益工资 2000 
001.001 张三 工龄工资 1000 
001.001 张三 房帖 800 
001.002 李四 岗位工资 3200 
001.002 李四 效益工资 1800 
001.002 李四 工龄工资 800 
001.002 李四 房帖 450 问题补充：用下面的语句生成测试数据 
create table 表1 (身份证号 varchar(7),姓名 varchar(8),编号 varchar(7)) 
create table 表2 (姓名 varchar(8),身份证号 varchar(7),岗位工资 decimal(8,2),效益工资 decimal(8,2) ,工龄工资 decimal(8,2) ,房帖 decimal(8,2)) insert 表1 values ('111111', '张三', '001.001') 
insert 表1 values ('111112', '李四', '001.002') 
insert 表1 values ('111113', '王五', '001.003') insert 表2 values('张三', '111111', 4300, 2000, 1000 , 500) 
insert 表2 values ('李四', '111112' ,3200, 1800 , 800, 450 ) 
insert 表2 values ('王五', '111113' , 3000 , 1600 , 700 , 400) 还有一个特别重要的事情就是表2的工资项目有可能以后会添加,比如会添加高温补贴等.

Answer 1

declare @str varchar(4000)
select @str=isnull(@str,'')+
'
union all
select  表1.编号,表1.姓名,'''+[name]+''' 工资项目,表2.'+[name]+' from 表1,表2 where 表1.身份证号=表2.身份证号'
from syscolumns where id=object_id('表2')
and [name] not in('姓名','身份证号')set @str=substring(@str,14,4000)
exec(@str)

Answer 2

create table 表1 (身份证号 varchar(7),姓名 varchar(8),编号 varchar(7))  
create table 表2 (姓名 varchar(8),身份证号 varchar(7),岗位工资 decimal(8,2),效益工资 decimal(8,2) ,工龄工资 decimal(8,2) ,房帖 decimal(8,2))  insert 表1 values ('111111', '张三', '001.001')  
insert 表1 values ('111112', '李四', '001.002')  
insert 表1 values ('111113', '王五', '001.003')  insert 表2 values('张三', '111111', 4300, 2000, 1000 , 500)  
insert 表2 values ('李四', '111112' ,3200, 1800 , 800, 450 )  
insert 表2 values ('王五', '111113' , 3000 , 1600 , 700 , 400)  
go
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('表2') and Name not in('姓名','身份证号') 
order by Colid
exec('select a.编号,a.姓名,b.[项目],b.[金额] from 表1 a inner join(
select 身份证号,[项目],[金额] from 表2 unpivot ([金额] for [项目] in('+@s+'))t
)b on a.身份证号=b.身份证号')
/*
编号      姓名       项目                    金额
------- -------- -------------------------- ---------------------------------------
001.001 张三       岗位工资                  4300.00
001.001 张三       效益工资                  2000.00
001.001 张三       工龄工资                  1000.00
001.001 张三       房帖                    500.00
001.002 李四       岗位工资                  3200.00
001.002 李四       效益工资                  1800.00
001.002 李四       工龄工资                  800.00
001.002 李四       房帖                    450.00
001.003 王五       岗位工资                  3000.00
001.003 王五       效益工资                  1600.00
001.003 王五       工龄工资                  700.00
001.003 王五       房帖                    400.00(12 行受影响)*/
go
drop table 表1,表2

Answer 3

create table 表1 (身份证号 varchar(7),姓名 varchar(8),编号 varchar(7))  
create table 表2 (姓名 varchar(8),身份证号 varchar(7),岗位工资 decimal(8,2),效益工资 decimal(8,2) ,工龄工资 decimal(8,2) ,房帖 decimal(8,2))  insert 表1 values ('111111', '张三', '001.001')  
insert 表1 values ('111112', '李四', '001.002')  
insert 表1 values ('111113', '王五', '001.003')  insert 表2 values('张三', '111111', 4300, 2000, 1000 , 500)  
insert 表2 values ('李四', '111112' ,3200, 1800 , 800, 450 )  
insert 表2 values ('王五', '111113' , 3000 , 1600 , 700 , 400)  
go
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select 身份证号,[项目]='+quotename(Name,'''')+',[金额]='+quotename(Name)+' from 表2 '
from syscolumns where ID=object_id('表2') and Name not in('姓名','身份证号') 
order by Colid
exec('select a.编号,a.姓名,b.[项目],b.[金额] from 表1 a inner join('+@s+')b on a.身份证号=b.身份证号')
/*
编号      姓名       项目       金额
------- -------- -------- ---------------------------------------
001.001 张三       岗位工资     4300.00
001.001 张三       效益工资     2000.00
001.001 张三       工龄工资     1000.00
001.001 张三       房帖       500.00
001.002 李四       岗位工资     3200.00
001.002 李四       效益工资     1800.00
001.002 李四       工龄工资     800.00
001.002 李四       房帖       450.00
001.003 王五       岗位工资     3000.00
001.003 王五       效益工资     1600.00
001.003 王五       工龄工资     700.00
001.003 王五       房帖       400.00(12 行受影响)
*/
go
drop table 表1,表2

Answer 4

create table 表1 
(身份证号 varchar(7),
姓名 varchar(8),
编号 varchar(7))
  
create table 表2 
(姓名 varchar(8),
身份证号 varchar(7),
岗位工资 decimal(8,2),
效益工资 decimal(8,2) ,
工龄工资 decimal(8,2) ,
房帖 decimal(8,2))  insert 表1 values ('111111', '张三', '001.001')  
insert 表1 values ('111112', '李四', '001.002')  
insert 表1 values ('111113', '王五', '001.003')  insert 表2 values('张三', '111111', 4300, 2000, 1000 , 500)  
insert 表2 values ('李四', '111112' ,3200, 1800 , 800, 450 )  
insert 表2 values ('王五', '111113' , 3000 , 1600 , 700 , 400)
go
declare @str varchar(max)=''
set @str='select * from ('+CHAR(10)
set @str=@str+'select a.编号,''岗位工资'' as 非固定工资项目,岗位工资 as 金额 from 表1 a join 表2 b on a.身份证号=b.身份证号 and a.姓名=b.姓名'+CHAR(10) 
select  @str=@str+'union all select a.编号,'''+name+''' as 非固定工资项目,b.'+name +' as 金额 from 表1 a join 表2 b on a.身份证号=b.身份证号 and a.姓名=b.姓名 '+CHAR(10) from syscolumns where id=OBJECT_ID('表2') and name not in('姓名','身份证号','岗位工资')
set @str=@str+')a order by 编号'
exec  (@str)