One-to-Multiple SQL statement syntax for fields between SQL SERVER tables and tables, serversql

One-to-Multiple SQL statement syntax for fields between SQL SERVER tables and tables, serversql

Table

A1	A2	A3	A4
01	02	03	04
03	04	01	02

Table B

B1	B2
01	Zhang San
02	Li Si
03	Wang Wu
04	Zhao Liu

Display result

A1	A2	A3	A4
Zhang San	Li Si	Wang Wu	Zhao Liu
Wang Wu	Zhao Liu	Zhang San	Li Si

:

The SQL statement is as follows:

SELECT  (SELECT B2 FROM B WHERE B1=A1) AS A1, (SELECT B2 FROM B WHERE B1=A2) AS A2, (SELECT B2 FROM B WHERE B1=A3) AS A3, (SELECT B2 FROM B WHERE B1=A4) AS A4 FROM A 

So OK.