Structural change/grouping with SELECT

Question

MySQL

There is a table in MySQL:

name	phone	phone type
Ivan	01	home
Ivan	02	mobile
Vladimir	03	home

You need to make a selection that will return this:

name	home	mobile
Ivan	01	02
Vladimir	03	NULL (or empty value)

That is, each unique phonetype value is associated with a new column in the resulting table, the value of which is taken from phone.
Intuitively, I understand that I need to somehow look towards GROUP BY and joins, but I can’t figure out exactly how to do this. Please help with advice on how this can be done

Answer 1

select name
   ,max(case when phone_type = 'home' then phone end) as home
   ,max(case when phone_type = 'mobile' then phone end) as mobile
from table
group by name

Answer 2

It's also possible like this:

SELECT
    home.name AS name,
    home.phone AS home,
    mobile.phone AS mobile
FROM tablename home
OUTER JOIN tablename mobile ON home.name = mobile.name
WHERE home.phonetype = 'home'
AND mobile.phonetype = 'mobile'

Answer 3

I would also advise making the phonetype field of the set type, and listing all the values ​​​​clearly there - then it will work faster. And then the query will need to be rebuilt simultaneously with the table alter - there is less chance of accidentally forgetting something.

Answer 4

Well, it's just not possible to do it through a join and a group buy. ;-)
Such things are done like this:
rows = db_cursor.query("SELECT * FROM ... ORDER BY name.");
# single row structure
#row_entry = {
# 'name': None
# 'home': None
# 'mobile': None
#}
iterate through all rows from rows. Keep changing "name". As soon as changes = new element. For each new element, we make a row_entry structure.
Further, this can be done with the help of cursors and procedurals in PL/SQL.