CODEWITHSUNDEEP
phpmysql
Md. Himel Ali
Md. Himel Ali

Reputation: 311

MySQL Pivot table Query in dynamic rows

I have two tables. Just like

----UserTable---- 
id  user    email       
1   admin   [email protected]
2   editor  [email protected]

----NameTable---- 
name    userid  fullname    mobile
own         1   Rahim       012314
father      1   Karim       120120
mother      1   Florin      212021
own         2   Masum       012314
father      2   Nahid       120120
mother      2   Zane        212021

How to fetch data all (Just Like name, Father, Mother, Own name) data in single query in mysql?

----Output Table----
id  user    email               name        fathername  mothername
1   admin   [email protected]     Rahim       Karim       Florin
2   editor  [email protected]    Masum       Nahid       Zane

Upvotes: 1

Views: 352

Answers (1)

sagi
sagi

Reputation: 40481

You dont have to use pivot in case there are always maximum of 3 columns(own,father and mother in this case)

SELECT t.id,t.user,t.email,
       max(case when s.name = 'own' then s.fullname end) as name,
       max(case when s.name = 'father' then s.fullname end) as fathername,
       max(case when s.name = 'mother' then s.fullname end) as mothername
FROM UserTable t
INNER JOIN NameTable s ON(t.id = s.user_id)

Upvotes: 3

Related Questions