mysql rows to columns, text type
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.
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
精彩评论