CODEWITHSUNDEEP
mysqlsqldatabase
Seegan See
Seegan See

Reputation: 101

Table column to row mysql query

I have 2 tables i need the result table almost converted from column to row converted

Table 1 : master_table

  master_id    user_name 

  1            name1 

  2            name2 

  3            name3 

  4            name4

Table 2 : master_meta_table

  id   master_id        meta_key         meta_value

  1    1                f_name           fname1

  2    2                f_name           fname2

  3    2                l_name           lname2

  4    2                age              age2

  5    3                l_name           lnam3

  6    3                age              age3

  7    4                sex              male

I would like to get the result like this 

  master_id    user_name     f_name     l_name   age    sex

  1            name1         fname1                        

  2            name2         fname2     lname2   age2                        

  3            name3                    lname3   age3                      

  4            name4         fname4                     male

Please some one help me with this...

Upvotes: 0

Views: 102

Answers (2)

szakwani
szakwani

Reputation: 384

Here is your DDL

create table master_table
(
  master_id int,
  user_name varchar(20)
  );

create table master_meta_table
(
  id int,
  master_id int,
  meta_key varchar(20),
  meta_value varchar(20)
  );


insert into master_table
values (
  1,'name1');
insert into master_table
values (
  2,'name2');
insert into master_table
values (
  3,'name3');
insert into master_table
values (
  4,'name4');

insert into master_meta_table
values (
  1,1,'f_name','fname1');
insert into master_meta_table
values (
  2,2,'f_name','fname2');
insert into master_meta_table
values (
  3,2,'l_name','lname2');
insert into master_meta_table
values (
  4,2,'age','age2');
insert into master_meta_table
values (
  5,3,'l_name','lname3');
insert into master_meta_table
values (
  6,3,'age','age3');
insert into master_meta_table
values (
  7,4,'sex','male');

your query

select
   mt.master_id,
   mt.user_name,
   max (case when mmt.meta_key = 'f_name' then mmt.meta_value end) as fname,
   max (case when mmt.meta_key = 'l_name' then mmt.meta_value end) as lname,
   max (case when mmt.meta_key = 'age' then mmt.meta_value end) as age,
   max (case when mmt.meta_key = 'sex' then mmt.meta_value end) as sex
from
   master_table mt,master_meta_table mmt 
where mt.master_id = mmt.master_id
group by mt.master_id,mt.user_name

http://sqlfiddle.com/#!4/55845/8

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select
   mt1.master_id,
   mt1.user_name,
   max(case when mt2.meta_key = 'f_name' then mt2.meta_value end) as fname,
   max(case when mt2.meta_key = 'l_name' then mt2.meta_value end) as lname,
   max(case when mt2.meta_key = 'age' then mt2.meta_value end) as age,
   max(case when mt2.meta_key = 'sex' then mt2.meta_value end) as sex
from
   master_table mt1
join master_meta_table mt2 on mt1.master_id = mt2.master_id
group by
   mt1.master_id,
   mt1.user_name

Fiddle: http://sqlfiddle.com/#!2/af277e/3/0

Upvotes: 2

Related Questions