0

I'm currently running into a problem where I am required to pivot many rows into a single row, defined by a specific key. I know the GROUP_CONCAT functionality and am using that currently, but I would like to avoid having to use explode in PHP after fetching my results. My table looks like this:

----------------------------------
| primary_id | key_value | value |
----------------------------------
| 1          | my_key_1  | val_1 |
| 1          | my_key_2  | val_2 |
| 1          | my_key_3  | val_3 |
| 2          | my_key_4  | val_4 |
| 2          | my_key_5  | val_5 |
----------------------------------

And I would like to build a MySQL-Query that presents this exactly like this for primary id 1 and primary id 2:

-----------------------------------------------
| primary_id | my_key_1 | my_key_2 | my_key_3 |
-----------------------------------------------
| 1          | val_1    | val_2    | val_3    |
-----------------------------------------------

------------------------------------
| primary_id | my_key_4 | my_key_5 |
------------------------------------
| 2          | val_4    | val_5    |
------------------------------------

So I can retrieve the output as an array in PHP, with the form:

$output[1][my_key_1] = val_1
$output[1][my_key_2] = val_2
...

The GROUP_CONCAT functionality works, but it would be much nicer to just have the value in the form I needd it only using SQL.

Would appreciate any pointers, best regards.

N. M.
  • 567
  • 5
  • 15
  • you could [normalize your DB](http://www.studytonight.com/dbms/database-normalization.php) to avoid exactly these kind of problems. – kscherrer Dec 01 '17 at 10:28
  • It's a Wordpress default table, can't normalize that or at least shouldn't. – N. M. Dec 01 '17 at 10:33
  • Possible duplicate of [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – Raymond Nijland Dec 01 '17 at 11:15
  • The values in the "key_value"-column can be whatever and are not just a set of 3 different one's, so it kind of differs my question a little from that topic. – N. M. Dec 01 '17 at 11:18

1 Answers1

0

Use a Pivot

select max(case (when key_value=my_key_1 then val_1 end)), max(case (when key_value=my_key_2 then val_2 end)), from... etc...

Philippe Merle
  • 115
  • 2
  • 4
  • 13
  • That's a possible solution, but I'd have to statically list each field, which I want to avoid. – N. M. Dec 01 '17 at 10:32
  • do you have a query object language uptop of SQL (like hql, jpql) you could use to make a key_value object? I did such a thing with hibernate, a first request for a list of key_value and passing this list as a parameter of the second request with the pivot. Resulted in no static reference. – Philippe Merle Dec 01 '17 at 10:55