两张表生成一张表，有点难度，谢谢！！
我有两张表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)
谢谢
[解决办法]
你说的和你要的结果不一样啊！
[解决办法]
--查
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
[解决办法]
这么快！
[解决办法]
create table tab1(id int,id1 varchar(10),name varchar(10))
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.id1
drop 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)
*/
[解决办法]
select
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
[解决办法]
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 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.pk
Select * From tab
GO
--除境
Drop Table tab, tab1, tab2
--果
/*
namepkq1q2q3q4
kkpk0q1q200
kkpk10q2q30
wwpk0q1q200
wwpk10q2q30
*/