Consulting a problem bosses
I have two table, a table structure as follows
A
Test_id name
Zhang SAN 1

Table B

Id test_id type name

1 1 1, dick, and harry2 1 2 fifty


B table according to the type, there are two different stages of the acceptance, excuse me how can I through the left link in them are found out in a record, thank you thank you!

CodePudding user response:

Hope is found out the results of the
Test_id name type_a name_a type_b name_b
So, thank you thank you, baidu for a long time didn't find a way

CodePudding user response:

 
The create table A table (test_id int, name varchar (10)) 

Insert into A table (test_id, name) 
Select 1, 'zhang' 

The create table table B (id int, test_id int, type int, name varchar (10)) 

Insert into table B (id, test_id, type, name) 
Select 1,1,1, 'bill' union all 
Select 2,1,2, 'Cathy'; 


As with t (
The select a.t est_id, a.n ame, b.t ype, qname=b.n ame 
From A table A 
The join table B B on a.t est_id=b.t est_id 
) 
The select test_id, 
The name, 
Type_a=1, 
Name_a=[1], 
Type_b=2, 
Name_b=[2] 
The from t 
The pivot (Max (qname) for the type (in [1], [2])) 
 b
/* 
Test_id name type_a name_a type_b name_b 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 zhang SAN li si 2 fifty 

1 row (affected) 
*/