列转行更新数据

[飞驴]

数据源
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

[百年树人]

if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [ta]
select 'u1','a','b','c' union all
select 'u2','d','e','f'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))
insert [tb]
select 'u1','E1','M' union all
select 'u1','E2','N' union all
select 'u1','E3','P' union all
select 'u2','E1','K' union all
select 'u2','E2','L' union all
select 'u2','E3','P'
go

update b
set b.vol=a.e1
from tb b,
(
select [user],'E1' as type,E1 from ta
union all
select [user],'E2' as type,E2 from ta
union all
select [user],'E3' as type,E3 from ta
) a
where a.type=b.type and a.[user]=b.[user]

select * from tb
/**
user Type VOL
---- ---- ----
u1   E1   a
u1   E2   b
u1   E3   c
u2   E1   d
u2   E2   e
u2   E3   f

(6 行受影响)
**/

[tjs_125]

if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [ta]
select 'u1','a','b','c' union all
select 'u2','d','e','f'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user] varchar(2),[Type] varchar(2),[VOL] varchar(1))
insert [tb]
select 'u1','E1','M' union all
select 'u1','E2','N' union all
select 'u1','E3','P' union all
select 'u2','E1','K' union all
select 'u2','E2','L' union all
select 'u2','E3','P';

--使用unpivot和基于连接的更新语句
--需要SQL2005才支持
update b
set b.VOL = a.VOL
from 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   a
u1   E2   b
u1   E3   c
u2   E1   d
u2   E2   e
u2   E3   f
*/

[小天]

[Quote=引用 1 楼 的回复:]
SQL code
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([user] varchar(2),[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [ta]
select 'u1','a','b','c' union all
se……
[/Quote]
犀利