How to union rows on the table mysql


Problem :

I have this values on the table:

id|field_id|value_text
1|  1|  msk 
1|  2|  anton
1|  4|  adm@site.com
2|  1|  spb
2|  2|  serg
2|  4|  rr@sk.ru
3|  1|  rng
3|  2|  moya
3|  4|ore@kole.com

How can I get this like:

id|city|name|email
1|msk|anton|adm@site.com
2|spb|serg|rr@sk.ru

Please help!



Solution :

This looks like you want to pivot the table

Try

SELECT 
    id, 
    MAX(CASE WHEN field_id = 1 THEN value_text END) AS city, 
    MAX(CASE WHEN field_id = 2 THEN value_text END) AS name, 
    MAX(CASE WHEN field_id = 4 THEN value_text END) AS email
FROM tbl
GROUP BY id

SQL Fiddle


Mysql Tutorials

Mysql Howto..

How to use GROUP By and HAVING in MySQL

how to sort mysql two different values?

how to set django and mysql work with UTF-8

How to dump database to a remote server?

How to select the lowest value from selection

I don't understand this piece of MySQL notation and subsequently don't know how to do it in Postgresql

How to select all records that are 10 minutes within current timestamp in MySQL?

How to show only few lines from a body text

How to encode json data for database storage in MySQL?

How do forums show you unread topics?

How to Determine Optimal MySQL Table Indexes, When Contents of WHERE Clause Vary?

How do I implement SQL_CALC_FOUND_ROWS on a query with UNION?

How to store search criteria or search results?

How do I skip an item in while loop if variable empty?

How to maintain timezones in multi datacenter replication of mysql

How to publish php website under GNU GPL, but keep MySQL password?

How to get text before space from MYSQL to Java

How to list the top 10 most duplicate rows?

How to restore Mysql Backup from dump file?

How to fix MySQL CREATE FUNCTION query?

How to query wp_usermeta using WordPress inbuilt functions efficiently?

How to import latitude/longitude data correctly into MySQL?

How to total up entries in a database

How to add QBE filters to SQL statement

How do I escape % from python mysql query

How to pass comma separated values to stored procedure in MySql?

how to echo table in php by mysql

Delphi: How to check if MySQL is installed on windows machine

How to catch error 1062 “duplicate entry” independent from used database/engine?

How to give a specific name to foreign key?