What is the correct way to write such a SQL query?

Question

MySQL

There are 2 tables. Such a query is required that will receive the combined data from two tables, while the fields (columns) of the resulting table will be the values ​​​​of one of the tables (an example of which I want is approximately shown in the picture, it is difficult to describe). Please help anyone who can. Where to dig? What will be applied? Which way to look? If there is an example - I will be grateful! ))

Answer 1

The task nontrivial, it is the best of all to write procedure.
Oracle has PIVOT and UNPIVOT functions (data transposition), they have implementations for older versions as separate procedures, try digging in that direction.
And in general, google transposition in MySQL, for example: buysql.com/mysql/14-how-to-automate-pivot-tables.html

Answer 2

Select
(select a1 from t1 where t1.id = t2.a1) as 1
(select a2 from t1 where t1.id = t2.a2) as 2
(select a3 from t1 where t1.id = t2.a3) as 3
from t2