Key-Value-Pair, Column for each key, keys as columnheader

Question

I have a table field_list like this:

field_id|obj_id|field_key|field_content
----------------------------------------    
     1  |   1  |   123   |   Title_A
     2  |   1  |   234   |   IP_A
     3  |   1  |   567   |   DNS_A
     4  |   2  |   123   |   Title_B
     5  |   2  |   456   |   IP_B
     6  |   2  |   789   |   DNS_B
     7  |   3  |   123   |   Title_C
     8  |   3  |   456   |   IP_C
     9  |   3  |   789   |   DNS_C

What I need is this:

obj_id  |123    |456  |789
----------------------------------------    
    1   |Title_A|IP_A |DNS_A
    2   |Title_B|IP_B |DNS_B
    3   |Title_C|IP_C |DNS_C

I had several ideas, how to achieve this (create new table or view, use scirpts, etc.). Unfortunately my SQL-Skills are not very professional and I never know if I am working in the right direction.

Background: The mySql database is used for storing information about webpages. There is a table 'obj', and a table 'field_list'. 'obj_id' is foreign-key from obj table. 'field_id' is primary-key from field_list.

I managed to get something that looks what I want with concatenate and seperators, but not as result with many columns.

If there is no easy answer, or to many - perhaps someone can lead me into the right direction.

Please excuse my bad english and grammar. This is my first stackoverflow question - I hope you have patience with me. I will modify my question tomorrow if something is not clear.

THX!

So what queries have you attempted thus far to achieve this result. Do you actually need that sort of pivot result in the query itself, or can you simply read the results into a multi-dimensional array in your application layer? — Mike Brant
– Mike Brant, Commented Jan 16, 2014 at 16:44
MySQL does not support this kind of EAV relations on it's own. I would suggest you compile your table in your script rather than your SQL statement. — ToBe
– ToBe, Commented Jan 16, 2014 at 17:07

ToBe · Accepted Answer · 2014-01-16 17:11:48Z

3

You could try something like this, but performance might not be perfect...

This table model excells in searching, not on it's table column representation:

SELECT object_id,
MAX( IF( field_key = 123, field_content, NULL) ) AS col_123,
MAX( IF( field_key = 234, field_content, NULL) ) AS col_234,
MAX( IF( field_key = 345, field_content, NULL) ) AS col_345,
MAX( IF( field_key = 456, field_content, NULL) ) AS col_456,
MAX( IF( field_key = 567, field_content, NULL) ) AS col_567
FROM field_list
GROUP BY object_id

answered Jan 16, 2014 at 17:11

ToBe

2,6791 gold badge20 silver badges31 bronze badges

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

6 Comments

Mutant Over a year ago

Thank you very much, this is indeed what i need or at least asked for. I tested your query in SQL Fiddle and it works very nice [sqlfiddle.com/#!2/5e313/10/0]. I will try to solve my problem at work with this tomorrow. I found something similiar when I googled my problem but that solution did not work in my DB. I hope this helps me to figure out why. Thank you very much!

Mutant Over a year ago

sqlfiddle.com/#!2/5e313/10/0

Mutant Over a year ago

I figured out, that this does not work with my phpmyadmin but sql-workbench gives me the result I was looking for. Dunno why this does not work with my phpmyadmin instance. But thanks again for this fast and nice answer!

ToBe Over a year ago

Strange. There schouldn't be a difference with phpMyAdmin... Anyways, glad I could help ;)

Mutant Over a year ago

The reason why MAX IF did not work with phpmyadmin was missing privileges. Phpmyadmin was connecting with user root from localhost while sql-workbench was using a different user from another machine. If someone has the same problem, try altering the table 'user' and grant missing privileges.

|

Collectives™ on Stack Overflow

Key-Value-Pair, Column for each key, keys as columnheader

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related