mySQL query for a table with multiple keys

Question

I have a table that has different rows, each row has a different key for the same items. I would like to create a mySQL query that puts the different keys as columns in a new table. Original table:

id  locale  key         value
1   en  description Great trip around cordoba's jklfjklfsjfdsk sdjk ds...
1   en  highlights_1    horse back riding
1   en  highlights_2    Asado
1   en  highlights_3    Free drinks

Requested result:

id  description        highlights_1        highlights_2       highlights_3    ...
1   Great trip ar...   horse back riding   Asado              Free drinks

The query I made (and gets me only one key/column in the new table is...)

SELECT activity.id,
  activity_multilingual_fields_text.value AS activitydescription
  FROM activity
  LEFT JOIN activity_multilingual_fields_text ON activity_multilingual_fields_text.id = activity.id  AND activity_multilingual_fields_text.locale = "en" 
GROUP BY activity.id

I would appreciate your help!

This is called a "pivot query". Do you know the exact number of possible values for key, or is it dynamic? If dynamic, you must create a dynamic SQL statement in application code or a stored procedure. Either way, there are many answers around here addressing it. — Michael Berkowski
– Michael Berkowski, Commented Mar 6, 2014 at 21:04
@Milen Pavlov Thanks for editing... Don't know yet how to style here, I've tried to add images, but don't have enough reputation points yet :-( — JNewbie
– JNewbie, Commented Mar 6, 2014 at 21:26
@MichaelBerkowski There are 5 possible values for key: description, highlights_1, highlights_2, highlights_3, highlights_4 – — JNewbie
– JNewbie, Commented Mar 6, 2014 at 21:33

Michael Berkowski · Accepted Answer · 2014-03-06 21:39:26Z

1

This is ultimately not difficult since there is a fixed and finite number of possible values for key. You do not need to dynamically construct the SQL, it can be hard-coded.

The general pattern is to enclose a CASE statement which returns the value column if the value of key matches a predefined string. This needs to be done for each possible key.

That results in 5 separate rows, mostly NULL though. So then you wrap each CASE inside a MAX() aggregate which discards the NULLs and collapses each into a single row. A GROUP BY must be applied over the other columns selected.

SELECT
  activity.id,
  MAX(CASE WHEN lf.`key` = 'description' THEN lf.value ELSE NULL END) AS `description`,
  MAX(CASE WHEN lf.`key` = 'highlights_1' THEN lf.value ELSE NULL END) AS `highlights_1`,
  MAX(CASE WHEN lf.`key` = 'highlights_2' THEN lf.value ELSE NULL END) AS `highlights_2`,
  MAX(CASE WHEN lf.`key` = 'highlights_3' THEN lf.value ELSE NULL END) AS `highlights_3`,
  MAX(CASE WHEN lf.`key` = 'highlights_4' THEN lf.value ELSE NULL END) AS `highlights_4`
FROM 
  activity
  LEFT JOIN activity_multilingual_fields_text lf
    ON lf.id = activity.id
       AND lf.locale = "en" 
GROUP BY activity.id

Here's a demonstration: http://sqlfiddle.com/#!2/bc028/3

answered Mar 6, 2014 at 21:39

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

JNewbie Over a year ago

Thanks! That did the trick. Small clarification for newbies like me, since it took me a minute to understand - Change "lf" to the name of the table...

Michael Berkowski Over a year ago

@JNewbie Yes, I initially left out the alias lf in the FROM clause but went back to add it. It is just an alias though to save repetitions of activity_multilingual_fields_text. In this case, both can actually be omitted because you have no other columns called value.

dcarrith · Accepted Answer · 2014-03-06 21:05:41Z

0

The GROUP BY activity.id is going to aggregate the results into 1 result record per unique value in the id column. That's probably why the code you have so far gets you only one key/column in the new table. Beyond that, the example doesn't seem to be complete. Where are you creating the table? Is that something you're doing with whatever server-side language you're using to execute the query?

answered Mar 6, 2014 at 21:05

dcarrith

7,9802 gold badges21 silver badges9 bronze badges

1 Comment

JNewbie Over a year ago

Hi, thanks for answering! I've tried it without the GROUP BY, it gives me 5 rows (as the number of keys) for each activity id, I want the keys as columns. I use this query on phpMyAdmin, I want to create the table and export it.

Collectives™ on Stack Overflow

mySQL query for a table with multiple keys

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related