MySQL: how to pivot unknown name-value pairs?

Say I have the following:

-------------------------------------
| id | user_id | name       | value |
=====================================
| 1  | 10      |开发者_JS百科 First Name | John  |
-------------------------------------
| 1  | 10      | Last Name  | Doe   |
-------------------------------------
| 1  | 10      | Age        | 25    |
-------------------------------------

Assume I do not know "First Name", "Last Name", and "Age" are the values in the name column of the table. How do I query this table so I get something like this:

------------------------------------------
| user_id | First Name | Last Name | Age |
==========================================
| 10      | John       | Doe       | 25  |
------------------------------------------

Mysql doesn't have a built-in way to solve this in an easy way that I'm aware of. You'll need to create some logic. I believe that you'll have to create a stored procedure, or write some logic into your application's code that will do the following:

1. Query for all distinct "name" fields
2. Dynamically create and execute a query with all the fields pivoted as described at http://en.wikibooks.org/wiki/MySQL/Pivot_table

If you want to do this totally on the mysql side, you can use dynamic sql in a stored procedure as described at http://www.java2s.com/Code/SQL/Procedure-Function/EXECUTEdynamicSQLcommand.htm.

Here's a procedure that should work for you:

DROP PROCEDURE myProc;
delimiter $$
CREATE PROCEDURE myProc ()
BEGIN
    DECLARE this_name varchar(20);
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT distinct name FROM user_info;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @sql='select user_info_1.user_id';
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO this_name;
        IF  done THEN 
            LEAVE read_loop;
        END IF;

        SET @sql=CONCAT(@sql, ", (select value from user_info where user_info.user_id = user_info_1.user_id and user_info.name = '", this_name, "') as '", this_name, "'");
    END LOOP;
    SET @sql=CONCAT(@sql, ' from (select distinct user_id from user_info) user_info_1');
    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;
END$$

Now, this said, be careful with this database structure. Be careful that you're not over-engineering the problem. Also, you're giving up a lot of functionality the database could otherwise provide for you, and it will be very difficult, or impossible, to implement unique constraints, foreign keys, etc. Many CRM tools have tried to go down this path, with disastrous results, and instead today they usually just automatically ALTER the underlying table to add new columns as necessary. You may want to consider that approach instead.