Trying to do this table in SQL

Question

I am working with databases and I need to do something like this:

TABLE 1

name	id
abc	34
def	35
ghi	36

TABLE 2

name2	id2
jkl	34
mno	35
pqr	36

RESULT TABLE

	jkl	mno	pqr
abc
def
ghi

Is this possible? To be more clear, I have two DB, and I need the data of one as column and the data of another as rows

What did you do to try an solve this? did you google for 'pivot' because that's what you seem to need. (and what doe you want to do with `id` and `id2` ?) — Luuk, Nov 30 '21 at 12:33
I have a table of usernames, and a table of movie name and I want to associate each username to a movie name. The value will be the classification that the user gave to the movie — thaidy_04, Nov 30 '21 at 13:28
It's called a [tag:pivot]. In MySQL it's a notorious pain in the xxs neck. — O. Jones, Nov 30 '21 at 13:33

score 0 · Answer 1 · answered Nov 30 '21 at 13:31

Use Join and Group By

SELECT MAX(t1.name) AS NAME,
       IF(t2.name2 = 'jkl', NULL, NULL) AS jkl,
       IF(t2.name2 = 'mno', NULL, NULL) AS mno,
       IF(t2.name2 = 'pqr', NULL, NULL) AS pqr
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id2
GROUP BY t2.name2

demo in db<>fiddle

Trying to do this table in SQL

1 Answers1