Maybe something like this:
select
user_id
,max(if(meta_key = 'zip_code', meta_value, null)) as zip_code
,max(if(meta_key = 'first_name', meta_value, null)) as first_name
,max(if(meta_key = 'last_name', meta_value, null)) as last_name
from wp_usermeta
group by user_id;
-Travis
-----Original Message-----
From: MadTh [mailto:madan.feedb...@gmail.com]
Sent: Thursday, August 12, 2010 4:08 PM
To: mysql@lists.mysql.com
Subject: project/extract similar items type, inside a table field as if a
field itself
Hi,
There is a mysql table ( wordpress) as following, called wp_usermeta, where
field meta_key holds zip_code , first_name, last_name inside it ( should
have been separate fields to extract data easily)
mysql> desc wp_usermeta;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'zip_code' ORDER BY
user_id limit 1,3;
+----------+---------+----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+----------+------------+
| 278 | 15 | zip_code | 32501 |
| 297 | 16 | zip_code | 32501 |
| 316 | 17 | zip_code | 32504 |
+----------+---------+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'first_name' ORDER
BY user_id limit 1,3;
+----------+---------+------------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+------------+------------+
| 280 | 16 | first_name | Jesxxdx |
| 299 | 17 | first_name | maerer |
| 318 | 18 | first_name | Liddd |
+----------+---------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'last_name' ORDER
BY user_id limit 1,3;
+----------+---------+-----------+------------+
| umeta_id | user_id | meta_key | meta_value |
+----------+---------+-----------+------------+
| 281 | 16 | last_name | Oweccc |
| 300 | 17 | last_name | magfffff |
| 319 | 18 | last_name | Pedfs |
+----------+---------+-----------+------------+
3 rows in set (0.01 sec)
mysql>
Is it possible to exctract each items, zip_code , first_name, last_name
inside the field meta_key separately and list them as if each item is a
field through a single mysql query. Else, it seems we will have to extract
each file and then import that to a new table with a each of the field
created inside that table.
Result something like:
first_name last_name zip_code
Jesxxdx Oweccc 32501
maerer magfffff 32501
Liddd Pedfs 32504
Thakns
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
