How to turn rows into columns in a query?

Question

MySQL

There is a table like
UserId | field | Value
With values
​​1 | Password | 123
1 | email | [email protected]
2 | Password | qwe2
| email | [email protected]
2 | name | Sergey
I need to make queries to get:
All fields for a specific UserId (eg =1)
UserId | Password | Email
1 | 123 | [email protected]
All available
UserId fields | Password | email | Name
1 | 123 | [email protected] | NULL
2 | qwe | [email protected] | Sergey
There are certain Fields (eg Name)
UserId | Password | email | Name
2 | qwe | [email protected] | Sergey

Answer 1

SELECT * FROM mytable WHERE UserId=1
SELECT * FROM mytable WHERE 1 GROUP BY UserId
SELECT * FROM mytable WHERE Name IS NOT NULL

PS Did you immediately come here or at least tried to find information on the net?

Answer 2

What you have is an EAV model . What you want to do with it - turn it into a normal relation - can be called Pivot.
Here SO arrived in time with advice, just for your part, including MySQL:
stackoverflow.com/questions/8764290/what-is-best-p...
stackoverflow.com/questions/649802/how-to-pivot-a- ...

Answer 3

Something like this. Experiment with Null and aggregate function

select max(UserId), max(Password), max(EMail)
(Select Value as UserId, NULL as Password, NULL as EMail  from mytable where Field = 'UserId'
Select NULL, Value, NULL from mytable where Field = 'Password'
Select NULL, NULL, Value from mytable where Field = 'EMail')