重新提交：动态的字符串如何转为字段名

现我有以下两张表：

表1：
字段1 字段2 年度
xxx1 yyy1 01
xxx2 yyy2 03
xxx3 yyy3 04
xxx4 yyy4 02
xxx5 yyy5 07

表2：
字段1 Y1 Y2 Y3 Y4 Y5 Y6 Y7
xxx1 33 00 00 00 00 00 00
xxx2 00 00 44 00 00 00 00
xxx3 00 00 00 55 00 00 00
xxx4 00 11 00 00 00 00 00
xxx5 00 00 00 00 00 00 19

同时，表1和表2有一个共同的字段：字段1

我现在想在表1中加入字段3，字段3的取值逻辑是这样的：按照表1中的年度去表2的对应年度字段中取值

例如：表1第一个记录的年度字段是01，那么将表1和表2按照字段1连接后，新加入的字段3取对应的Y1的值，即：33；表1第二个记录的年度字段是03，那么新加入的字段3取Y3的值，即44。

最后得到的结果应该是这样：
字段1 字段2 年度 字段3
xxx1 yyy1 01 33
xxx2 yyy2 03 44
xxx3 yyy3 04 55
xxx4 yyy4 02 11
xxx5 yyy5 07 19


该如何实现呢？

SQL code

create table tb1
(字段1 varchar(4), 字段2 varchar(4), 年度 varchar(2))

create table tb2
(字段1 varchar(4),Y1 varchar(2),Y2 varchar(2),Y3 varchar(2),Y4 varchar(2),Y5 varchar(2),Y6 varchar(2),Y7 varchar(2))


insert into tb1
select 'xxx1', 'yyy1', '01' union all
select 'xxx2', 'yyy2', '03' union all
select 'xxx3', 'yyy3', '04' union all
select 'xxx4', 'yyy4', '02' union all
select 'xxx5', 'yyy5', '07' 

insert into tb2
select 'xxx1', '33', '00', '00', '00', '00', '00', '00' union all
select 'xxx2', '00', '00', '44', '00', '00', '00', '00' union all
select 'xxx3', '00', '00', '00', '55', '00', '00', '00' union all
select 'xxx4', '00', '11', '00', '00', '00', '00', '00' union all
select 'xxx5', '00', '00', '00', '00', '00', '00', '19' 

select t1.字段1,t1.字段2,t1.年度,t2.值 from tb1 t1 left join (
select 字段1,'01' AS 年度,Y1 AS 值 from tb2
union all
select 字段1,'02' AS 年度,Y2 AS 值 from tb2
union all
select 字段1,'03' AS 年度,Y3 AS 值 from tb2
union all
select 字段1,'04' AS 年度,Y4 AS 值 from tb2
union all
select 字段1,'05' AS 年度,Y5 AS 值 from tb2
union all
select 字段1,'06' AS 年度,Y6 AS 值 from tb2
union all
select 字段1,'07' AS 年度,Y7 AS 值 from tb2) t2 on t1.字段1=t2.字段1 and t1.年度=t2.年度

SQL code

create table tb1
(字段1 varchar(4), 字段2 varchar(4), 年度 varchar(2))

create table tb2
(字段1 varchar(4),Y1 varchar(2),Y2 varchar(2),Y3 varchar(2),Y4 varchar(2),Y5 varchar(2),Y6 varchar(2),Y7 varchar(2))


insert into tb1
select 'xxx1', 'yyy1', '01' union all
select 'xxx2', 'yyy2', '03' union all
select 'xxx3', 'yyy3', '04' union all
select 'xxx4', 'yyy4', '02' union all
select 'xxx5', 'yyy5', '07' 

insert into tb2
select 'xxx1', '33', '00', '00', '00', '00', '00', '00' union all
select 'xxx2', '00', '00', '44', '00', '00', '00', '00' union all
select 'xxx3', '00', '00', '00', '55', '00', '00', '00' union all
select 'xxx4', '00', '11', '00', '00', '00', '00', '00' union all
select 'xxx5', '00', '00', '00', '00', '00', '00', '19' 

select t1.字段1,t1.字段2,t1.年度,t2.字段3 from tb1 t1 left join (
select 字段1,'01' AS 年度,Y1 AS 字段3 from tb2
union all
select 字段1,'02' AS 年度,Y2 AS 字段3 from tb2
union all
select 字段1,'03' AS 年度,Y3 AS 字段3 from tb2
union all
select 字段1,'04' AS 年度,Y4 AS 字段3 from tb2
union all
select 字段1,'05' AS 年度,Y5 AS 字段3 from tb2
union all
select 字段1,'06' AS 年度,Y6 AS 字段3 from tb2
union all
select 字段1,'07' AS 年度,Y7 AS 字段3 from tb2) t2 on t1.字段1=t2.字段1 and t1.年度=t2.年度


---
字段1    字段2    年度    字段3
xxx1    yyy1    01    33
xxx2    yyy2    03    44
xxx3    yyy3    04    55
xxx4    yyy4    02    11
xxx5    yyy5    07    19

SQL code

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-10 13:59:13
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--    Jul  9 2008 14:43:34 
--    Copyright (c) 1988-2008 Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([字段1] varchar(4),[字段2] varchar(4),[年度] varchar(2))
insert [a]
select 'xxx1','yyy1','01' union all
select 'xxx2','yyy2','03' union all
select 'xxx3','yyy3','04' union all
select 'xxx4','yyy4','02' union all
select 'xxx5','yyy5','07'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([字段1] varchar(4),[Y1] varchar(2),[Y2] varchar(2),[Y3] varchar(2),[Y4] varchar(2),[Y5] varchar(2),[Y6] varchar(2),[Y7] varchar(2))
insert [b]
select 'xxx1','33','00','00','00','00','00','00' union all
select 'xxx2','00','00','44','00','00','00','00' union all
select 'xxx3','00','00','00','55','00','00','00' union all
select 'xxx4','00','11','00','00','00','00','00' union all
select 'xxx5','00','00','00','00','00','00','19'
--------------开始查询--------------------------
select
   a.*,b.字段3
from 
   a
join
(
select 字段1,coalesce(nullif(y1,'00'),nullif(y2,'00'), nullif(y3,'00'), nullif(y4,'00'), nullif(y5,'00'),nullif(y6,'00'),nullif(y7,'00')) as 字段3 from b)b
on
   a.字段1=b.字段1

----------------结果----------------------------
/* 字段1  字段2  年度   字段3
---- ---- ---- ----
xxx1 yyy1 01   33
xxx2 yyy2 03   44
xxx3 yyy3 04   55
xxx4 yyy4 02   11
xxx5 yyy5 07   19

(5 行受影响)
*/