sql - How to transform a MySQL table into another that has key value pairs? -

i have accomplish requirement in mysql, need convert regular table 1 key value pairs, unable find answers (i'm relatively new sql).

i've been able find answers approach that's opposite of requirement though - here's sample.

as part of database migration activity in project, source table looks this:

mysql> select * employee; +------+-------+--------+ | id   | fname | lname  | +------+-------+--------+ | 1    | lex   | luthor | | 2    | clark | kent   | | 3    | lois  | lane   | +------+-------+--------+ 3 rows in set (0.00 sec) 

now, has converted table looks below:

+----------------+-----------------+ | attribute_name | attribute_value | +----------------+-----------------+ | id             | 1               | | fname          | lex             | | lname          | luthor          | | id             | 2               | | fname          | clarke          | | lname          | kent            | | id             | 3               | | fname          | lois            | | lname          | lane            | +----------------+-----------------+ 

i've tried , gotten till below query results aren't quite there yet.

mysql> select     -> t.column_name attribute_name,     -> case t.column_name     -> when 'id' employee.id     -> when 'fname' employee.fname     -> when 'lname' employee.lname     -> end attribute_value     -> employee, information_schema.columns t t.column_name in     -> (select column_name information_schema.columns table_schema = 'data_migration_test_destination' , table_name = 'employee');                          +----------------+-----------------+ | attribute_name | attribute_value | +----------------+-----------------+ | id             | 1               | | id             | 1               | | id             | 1               | | fname          | lex             | | lname          | luthor          | | id             | 1               | | fname          | lex             | | lname          | luthor          | | id             | 1               | | id             | 2               | | id             | 2               | | id             | 2               | | fname          | clark           | | lname          | kent            | | id             | 2               | | fname          | clark           | | lname          | kent            | | id             | 2               | | id             | 3               | | id             | 3               | | id             | 3               | | fname          | lois            | | lname          | lane            | | id             | 3               | | fname          | lois            | | lname          | lane            | | id             | 3               | +----------------+-----------------+ 27 rows in set (0.05 sec) 

not sure why id pops in result , why there multiple repetitions other key value pairs (id, fname , lname). ideally expected row count has 9, 27!

try this

                mysql>  create table #test                     (                         [id] int,                         fname  nvarchar(60),                         lname nvarchar(60)                     )                     insert #test values                      (1,          'lex',     'luthor'),                     (2,          'clark',     'kent'),                     (3,          'lois',    'lane')                       select * #test                      select t2.n.value('local-name(.)', 'nvarchar(128)') [key],                            t2.n.value('text()[1]', 'nvarchar(max)') value                     (select *                           #test                           xml path(''), type) t1(x)                       cross apply t1.x.nodes('/*') t2(n)                       drop table #test