CODEWITHSUNDEEP
mysql
Mr_Thomas
Mr_Thomas

Reputation: 869

MySQL select multiple fields using vertical data

I have a MySQL database where the table was set up to store the data in a vertical fashion: 

Userid -- Field -- Data
186       30       New York
186       31       Phone
186       32       Delta

187       30       Los Angeles
187       31       Website
187       32       US Air

I am able to select columns if they are already horizontal but want to be able to organize the output so it looks like this:

Userid -- Data30   --   Data31 -- Data32
186       New York      Phone     Delta
187       Los Angeles   Website   US Air

How do I say "Select DATA30 to show for this column but only if FIELD equals 30"?

Upvotes: 1

Views: 1343

Answers (2)

jcho360
jcho360

Reputation: 3759

Try this: 

select Userid, 
if(Field=30,Data,null),
if(Field=31,Data,null),
if(Field=32,Data,null) 
from table1
group by Userid;

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270607

This is a classic pivot table. Using MAX() aggregates and GROUP BY with CASE, you can convert the rows to columns. This will only work properly if the values for Field are limited and well-defined.

The purpose of the MAX aggregate is only to eliminate NULL for each value, so instead of 3 rows per Userid with only one of the values non-null, they are collapsed into a single row.

SELECT
  Userid,
  MAX(CASE WHEN Field = 30 THEN Data ELSE NULL END) AS Data30,
  MAX(CASE WHEN Field = 31 THEN Data ELSE NULL END) AS Data31,
  MAX(CASE WHEN Field = 32 THEN Data ELSE NULL END) AS Data32
FROM yourtable
GROUP BY Userid

Upvotes: 1

Related Questions