mysql rows to columns, text type

Question

If i have a table of data like

Option_id|Component_id|Option_parent|Option_name|Option_value
1         1            0             id          
2         1            1             option1     Some value
3         1            1             option2     Other
4         1            0             id          Value
5         1            4             option1     More
6         1            4             option2     More&More

Is it possible to return rows with the option_name as columns when providing the "option_name" to select and the component_id. The option_name with "id" will be the parent using it's "option_id".

So Select option1, option2 where Component_id = 1 returns

Option1    |Option2
Some Value  Other
More        More&More

I'm basically trying to see if i can have a generic table that can be used by components to store varying amounts of data. I know i can use joins but wondered if there might be a better way as one component could have 10 options.

Answer 1

Use:

  SELECT MAX(CASE WHEN t.option_name = 'option1' THEN t.option_value END) AS option1,
         MAX(CASE WHEN t.option_name = 'option2' THEN t.option_value END) AS option2
    FROM TABLE t
   WHERE t.option_name IN ('option1', 'option2')
GROUP BY t.component_id, t.option_parent