列转行更新数据,该如何解决

列转行更新数据
数据源
ta
user E1 E2 E3
u1 a b c
u2 d e f


更新表结构
tb
user Type VOL
u1 E1 M
u1 E2 N
u1 E3 P
u2 E1 K
u2 E2 L
u2 E3 P


如何用ta的内容更新掉tb的内容
最终得到结果
user Type VOL
u1 E1 a
u1 E2 b
u1 E3 c
u2 E1 d
u2 E2 e
u2 E3 f




[解决办法]

SQL code

if object_id('[ta]') is not null drop table [ta]gocreate table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))insert [ta]select 'u1','a','b','c' union allselect 'u2','d','e','f'goif object_id('[tb]') is not null drop table [tb]gocreate table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))insert [tb]select 'u1','E1','M' union allselect 'u1','E2','N' union allselect 'u1','E3','P' union allselect 'u2','E1','K' union allselect 'u2','E2','L' union allselect 'u2','E3','P'goupdate bset b.vol=a.e1from tb b,(select [user],'E1' as type,E1 from taunion allselect [user],'E2' as type,E2 from taunion allselect [user],'E3' as type,E3 from ta) awhere a.type=b.type and a.[user]=b.[user]select * from tb/**user Type VOL---- ---- ----u1   E1   au1   E2   bu1   E3   cu2   E1   du2   E2   eu2   E3   f(6 行受影响)**/
[解决办法]
SQL code
if object_id('[ta]') is not null drop table [ta]gocreate table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))insert [ta]select 'u1','a','b','c' union allselect 'u2','d','e','f'goif object_id('[tb]') is not null drop table [tb]gocreate table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))insert [tb]select 'u1','E1','M' union allselect 'u1','E2','N' union allselect 'u1','E3','P' union allselect 'u2','E1','K' union allselect 'u2','E2','L' union allselect 'u2','E3','P';--使用unpivot和基于连接的更新语句--需要SQL2005才支持update bset b.VOL = a.VOLfrom tb b    join (    select *            from ta            unpivot            (                Vol for [type]                in(E1, E2, E3)            ) pt        ) a    on b.[user] = a.[user] and b.Type = a.type;    select * from tb;/*user Type VOL---- ---- ----u1   E1   au1   E2   bu1   E3   cu2   E1   du2   E2   eu2   E3   f*/