我有两张表tab1(id,id1,name) tab2(id,id2,pk,q)
要求生成一张新表
tab(name,pk,q1,q2,q3,q4,)
其中tab1.id1=tab2.id2,其中q只有4种数据类型
tab1(1,kk,王)tab1(2,ww,李)
tab2(1,kk,pk0,q1) tab2(2,kk,pk1,q2)tab2(3,kk,pk0,q2)tab2(4,kk,pk1,q3)tab2(1,ww,pk0,q1) tab2(3,ww,pk1,q2)tab2(3,ww,pk0,q2)tab2(4,ww,pk1,q3)生成新表后的数据为
tab(kk,pk0,q1,q2,0,0)
tab(kk,pk1,0,q2,q3,0)tab(ww,pk0,q1,q2,0,0)
tab(ww,pk1,0,q2,q3,0)谢谢
要求生成一张新表
tab(name,pk,q1,q2,q3,q4,)
其中tab1.id1=tab2.id2,其中q只有4种数据类型
tab1(1,kk,王)tab1(2,ww,李)
tab2(1,kk,pk0,q1) tab2(2,kk,pk1,q2)tab2(3,kk,pk0,q2)tab2(4,kk,pk1,q3)tab2(1,ww,pk0,q1) tab2(3,ww,pk1,q2)tab2(3,ww,pk0,q2)tab2(4,ww,pk1,q3)生成新表后的数据为
tab(kk,pk0,q1,q2,0,0)
tab(kk,pk1,0,q2,q3,0)tab(ww,pk0,q1,q2,0,0)
tab(ww,pk1,0,q2,q3,0)谢谢
Select
A.id1,
B.pk,
Max(Case q When 'q1' Then 'q1' Else '0' End) As q1,
Max(Case q When 'q2' Then 'q2' Else '0' End) As q2,
Max(Case q When 'q3' Then 'q3' Else '0' End) As q3,
Max(Case q When 'q4' Then 'q4' Else '0' End) As q4
From
tab1 A
Left Join
tab2 B
On A.id1 = B.id2
Group By
A.id1,
B.pk
Select
A.id1,
B.pk,
Max(Case q When 'q1' Then 'q1' Else '0' End) As q1,
Max(Case q When 'q2' Then 'q2' Else '0' End) As q2,
Max(Case q When 'q3' Then 'q3' Else '0' End) As q3,
Max(Case q When 'q4' Then 'q4' Else '0' End) As q4
Into tab3 --加入此行代碼
From
tab1 A
Left Join
tab2 B
On A.id1 = B.id2
Group By
A.id1,
B.pk
insert tab1
select 1,'kk','王'
union select 2,'ww','李'create table tab2(id int,id2 varchar(10),pk varchar(10),q varchar(10))
insert tab2
select 1,'kk','pk0','q1'
union select 2,'kk','pk1','q2'
union select 3,'kk','pk0','q2'
union select 4,'kk','pk1','q3'
union select 1,'ww','pk0','q1'
union select 3,'ww','pk1','q2'
union select 3,'ww','pk0','q2'
union select 4,'ww','pk1','q3'
select
a.id1,
b.pk,
Max(Case q When 'q1' Then 'q1' Else '0' End) As q1,
Max(Case q When 'q2' Then 'q2' Else '0' End) As q2,
Max(Case q When 'q3' Then 'q3' Else '0' End) As q3,
Max(Case q When 'q4' Then 'q4' Else '0' End) As q4
from tab1 a Left Join tab2 b on a.id1 = b.id2
group by
a.id1,
b.pk
order by a.id1drop table tab1 , tab2
/*
id1 pk q1 q2 q3 q4
---------- ---------- ---- ---- ---- ----
kk pk0 q1 q2 0 0
kk pk1 0 q2 q3 0
ww pk0 q1 q2 0 0
ww pk1 0 q2 q3 0(4 row(s) affected)*/
a.id1,
b.pk,
min(Case q When 'q1' Then 'q1' Else '0' End) As q1,
min(Case q When 'q2' Then 'q2' Else '0' End) As q2,
min(Case q When 'q3' Then 'q3' Else '0' End) As q3,
min(Case q When 'q4' Then 'q4' Else '0' End) As q4
from tab1 a Left Join tab2 b on a.id1 = b.id2
group by
a.id1,
b.pk
order by a.id1
A.id1,
B.pk,
Max(Case q When 'q1' Then 'q1' Else '0' End) As q1,
Max(Case q When 'q2' Then 'q2' Else '0' End) As q2,
Max(Case q When 'q3' Then 'q3' Else '0' End) As q3,
Max(Case q When 'q4' Then 'q4' Else '0' End) As q4
Into tab
From
tab1 A
Left Join
tab2 B
On A.id1 = B.id2
Group By
A.id1,
B.pk
Create Table tab1(id Int, id1 Varchar(10), name Nvarchar(20))
Create Table tab2(id Int, id2 Varchar(10), pk Varchar(10), q Varchar(10))
--插入數據
Insert tab1 Select 1, 'kk', N'王'
Union All Select 2, 'ww', N'李'Insert tab2
Select 1, 'kk', 'pk0', 'q1'
Union All Select 2, 'kk', 'pk1', 'q2'
Union All Select 3, 'kk', 'pk0', 'q2'
Union All Select 4, 'kk', 'pk1', 'q3'
Union All Select 1, 'ww', 'pk0', 'q1'
Union All Select 3, 'ww', 'pk1', 'q2'
Union All Select 3, 'ww', 'pk0', 'q2'
Union All Select 4, 'ww', 'pk1', 'q3'
GO
--測試
--生成新表
Select
A.id1 As name,
B.pk,
Max(Case q When 'q1' Then 'q1' Else '0' End) As q1,
Max(Case q When 'q2' Then 'q2' Else '0' End) As q2,
Max(Case q When 'q3' Then 'q3' Else '0' End) As q3,
Max(Case q When 'q4' Then 'q4' Else '0' End) As q4
Into tab
From
tab1 A
Left Join
tab2 B
On A.id1 = B.id2
Group By
A.id1,
B.pk
Order By
A.id1,
B.pkSelect * From tab
GO
--刪除測試環境
Drop Table tab, tab1, tab2
--結果
/*
name pk q1 q2 q3 q4
kk pk0 q1 q2 0 0
kk pk1 0 q2 q3 0
ww pk0 q1 q2 0 0
ww pk1 0 q2 q3 0
*/