22,209
社区成员
发帖
与我相关
我的任务
分享
id eA1 aA1 eA2 aA2 eA3 aA3 eB1 aB1 eB2 aB2 eC1 aC1 eC2 aC2
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
100 ex1 ac1 ex2 ac2 ex3 ac3 ex5 ac5 NULL NULL NULL NULL NULL NULL
200 ex8 ac7 ex7 ac7 NULL NULL ex6 ac6 NULL NULL NULL NULL NULL NULL
300 ex1 ac1 ex2 ac2 ex3 ac3 NULL NULL NULL NULL ex5 ac5 NULL NULL
400 ex8 ac9 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(4 行受影响)
create table template(id int,ord_id int,[type] int,[desc] nvarchar(10))
insert into template
select 1,1,1,'A1' union all
select 2,2,1,'A2' union all
select 3,3,1,'A3' union all
select 4,1,2,'B1' union all
select 5,2,2,'B2' union all
select 6,4,2,'B3' union all
select 7,5,2,'B4' union all
select 8,1,3,'C1' union all
select 9,4,3,'C2'
create table detail(id int,ord_id int,[type] int,[desc] nvarchar(10),temlate_id int,exd nvarchar(10),act nvarchar(10))
insert into detail
select 100,1,1,'A1',1,'ex1','ac1' union all
select 100,2,1,'A2',2,'ex2','ac2' union all
select 100,3,1,'A3',3,'ex3','ac3' union all
select 100,1,2,'B1',4,'ex5','ac5' union all
select 200,1,2,'B1',4,'ex6','ac6' union all
select 200,1,2,'A2',2,'ex7','ac7' union all
select 200,1,1,'A1',1,'ex8','ac7' union all
select 300,1,1,'A1',1,'ex1','ac1' union all
select 300,2,1,'A2',2,'ex2','ac2' union all
select 300,3,1,'A3',3,'ex3','ac3' union all
select 300,1,3,'C1',8,'ex5','ac5' union all
select 400,2,1,'A1',1,'ex8','ac9'
go
select a.id,
(select top 1 exd from detail where id=a.id and [desc]='A1')as eA1,
(select top 1 act from detail where id=a.id and [desc]='A1')as aA1,
(select top 1 exd from detail where id=a.id and [desc]='A2')as eA2,
(select top 1 act from detail where id=a.id and [desc]='A2')as aA2,
(select top 1 exd from detail where id=a.id and [desc]='A3')as eA3,
(select top 1 act from detail where id=a.id and [desc]='A3')as aA3,
(select top 1 exd from detail where id=a.id and [desc]='B1')as eB1,
(select top 1 act from detail where id=a.id and [desc]='B1')as aB1,
(select top 1 exd from detail where id=a.id and [desc]='B2')as eB2,
(select top 1 act from detail where id=a.id and [desc]='B2')as aB2,
(select top 1 exd from detail where id=a.id and [desc]='C1')as eC1,
(select top 1 act from detail where id=a.id and [desc]='C1')as aC1,
(select top 1 exd from detail where id=a.id and [desc]='C2')as eC2,
(select top 1 act from detail where id=a.id and [desc]='C2')as aC2
from(
select distinct id from detail
)a
go
drop table template,detail
go