Re-mapping rows in MYSQL

Question

I have table like this:

uid | fid | value
1   | 1   | nameOf1
1   | 2   | surnameOf1
1   | 3   | countryOf1
2   | 1   | nameOf2
2   | 2   | surnameOf2

And need to transform/select it like this:

uid | name    | surname    | country
1   | nameOf1 | surnameOf1 | countryOf1
2   | nameOf2 | surnameOf2 | ..

possible duplicate of mysql pivot query results with GROUP BY — Ben
– Ben, Commented Nov 17, 2013 at 15:29

Alex · Accepted Answer · 2013-11-17 15:35:51Z

1

Try something like:

SELECT t1.uid, t2.value AS name, t3.value AS surname, t4.value AS country FROM table t1
  LEFT JOIN table t2 on t2.uid = t1.uid AND t2.fid = 1
  LEFT JOIN table t3 on t3.uid = t1.uid AND t3.fid = 2
  LEFT JOIN table t4 on t4.uid = t1.uid AND t4.fid = 3
  GROUP BY t1.uid;

or:

SELECT DISTINCT t1.uid, t2.value AS name, t3.value AS surname, t4.value AS country FROM table t1
  LEFT JOIN table t2 on t2.uid = t1.uid AND t2.fid = 1
  LEFT JOIN table t3 on t3.uid = t1.uid AND t3.fid = 2
  LEFT JOIN table t4 on t4.uid = t1.uid AND t4.fid = 3;

edited Nov 17, 2013 at 15:35

answered Nov 17, 2013 at 15:30

Alex

1,59311 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

I have made a test with 280,000 records (20% of countries are empty). Without indexes both queries are too slow. They almost do not work. Then I created a primary index on two columns: uid and fid. And both queries work more or less fast.

Brian Hoover · Accepted Answer · 2013-11-17 15:35:19Z

0

It's a little complex, but you could do something like:

select distinct uid as uid,
   (select value from dataTable as table1 
           where table1.uid=dataTable.uid and fid = 1) as name,
   (select value from dataTable as table2 
           where table2.uid=dataTable.uid and fid = 2) as surname,
   (select value from dataTable as table3 
           where table3.uid=dataTable.uid and fid = 3) as country
from dataTable group by uid

SQLFiddle

answered Nov 17, 2013 at 15:35

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

Re-mapping rows in MYSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related