Crosstab query sql statement

Table 1:
Group name
Member 1id
Member 2id
Member 3id

Sample data:
Charge team            2      3
Guard team     2      3       4

Table 2:
Member ID
Member Name

Sample data:
1     Zhang San
2     Li Si
3     Wang Wu
4     Lu Er

Result required

Charge team Zhang San Li Si Wang Wu
Guard team Li Si Wang Wu Lu Er

Copy the codeThe code is as follows:

--Create a test environment
Create   Table   Table 1 (group name   varchar(10), member 1id   varchar(10), member 2id  varchar(10), member 3id   varchar(10))
--Insert data
insert    into    Table 1
select   'Charge Group','1','2','3'  union
select   'Defense Team','2','3','4'
Create   Table   Table 2 (member id   varchar(10), member name  varchar(10))
--Insert data
insert    into   Table 2
select   '1','Zhang San'   union
select   '2','Li Si'  union
select   '3','Wang Wu'   union
select   '4','Lu Er'

-- Test statement
select   a. Group name,
Member 1=(select   Member name   from   Table 2   b  where  a.Member 1id=b.Member id),
Member 1=(select   Member name   from   Table 2   b  where  a.Member 2id=b.Member id),
Member 1=(select   Member name   from   Table 2   b  where  a.Member 3id=b.Member id)
from   Table 1   a

       
--Delete the test environment
Drop   Table   Table 1
Drop   Table   Table 2

  /*    
Group Name                                                                                                                              �
  ----------   ----------   ----------   ----------      
Charge team Zhang San Li Si Wang Wu
Guard Team

(The number of rows affected is 2 rows)
  */

Copy the codeThe code is as follows:

select      
a. Group name,
Member 1   =   max(case   b. Member id   =   a. Member 1id   then   b. Member name   end),
Member 2   =   max(case   b. Member id   =   a. Member 2id   then   b. Member name   end),
Member 3   =   max(case   b. Member id   =   a. Member 3id   then   b. Member name   end),
  from      
Table 1 a,
Table 2        B
  group   by    
a. Group name

Copy the codeThe code is as follows:

select      
a. Group name,
Member 1   =   max(case   b. Member id   =   a. Member 1id   then   b. Member name   end),
Member 2   =   max(case   b. Member id   =   a. Member 2id   then   b. Member name   end),
Member 3   =   max(case   b. Member id   =   a. Member 3id   then   b. Member name   end)
  from      
Table 1 a,
Table 2        B
  group   by    
a. Group name

Copy the codeThe code is as follows:

select   a. Group name,
Member 1=(select   Member name   from   Table 2   b  where  a. Member 1id=b. Member id),
Member 1=(select   Member name   from   Table 2   b  where  a. Member 2id=b. Member id),
Member 1=(select   Member name   from   Table 2   b  where  a.Member 3id=b.Member id)
from   Table 1   a

Copy the codeThe code is as follows:

The correct solution is
select   Table 1. Group name,
(select   Table 1. Member Name   from   Table 2     where Table 1. Member 1id=Table 2. Member id)       Member 1id,
(select   Table 1. Member Name   from   Table 2     where Table 1. Member 2id=Table 2. Member id)       Member 2id,
(select   Table 1. Member Name   from   Table 2     where Table 1. Member 3id=Table 2. Member id)       Member 3id
from   Table 1, Table 2