Convert mysql value into columns

Question

I am new to this community but if my question doesn't follow the rules, please feel free to close this. Thanks!

This is my php database query

$sql = "
SELECT meta_key
     , meta_value 
  FROM " . $wpdb->prefix . "postmeta 
 WHERE post_id = " . $value->post_id . " 
   AND meta_key LIKE '_field_%'";

This is the output so far

| meta_key  | meta_value |
| --------- | ---------- |
| _field_11 | Rosenie    |
| _field_30 | Tagarda    |
| _field_12 | Talibo     |
| _field_11 | Rondeo     |
| _field_30 | Soroysoroy |
| _field_12 | Balos      |

But I want it to be like this

| _field_11  | _field_30  | _field_12 |
| ---------- | ---------- | --------- |
| Rosenie    | Tagarda    | Talibo    |
| Rondeo     | Soroysoroy | Balos     |

Is there anyway to do this? Thanks in advance.

My temporary solution: https://pastebin.com/e5rF3ZQe

Just parse the output in a php loop - or use a bit of script to format the result — Strawberry, Dec 27 '20 at 12:20
How do you define which `_field_11` relates to which `_field_30` or `_field_12`? There should be some other column with a common value for these three rows. — GMB, Dec 27 '20 at 12:21
For your information. this operation is called [tag:pivot]ing the data. — O. Jones, Dec 27 '20 at 12:43

score 0 · Accepted Answer · edited Oct 24 '21 at 12:59

You can pivot your dataset. Assuming that you have a column that defines how rows should be combined, say some_id, you can do something like this

SELECT some_id, 
    max(case when meta_key = '_field_11' then meta_value end) as field_11,
    max(case when meta_key = '_field_30' then meta_value end) as field_30,
    max(case when meta_key = '_field_12' then meta_value end) as field_12
FROM t_postmeta 
WHERE post_id = ? AND meta_key LIKE '_field_%'
GROUP BY some_id

If you don't have that some_id column, then you need at least one column to order the rows, which we can use with row_number() - say ordering_id:

SELECT 
    max(case when meta_key = '_field_11' then meta_value end) AS field_11,
    max(case when meta_key = '_field_30' then meta_value end) AS field_30,
    max(case when meta_key = '_field_12' then meta_value end) AS field_12
FROM (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY meta_key ORDER by ordering_id) AS rn
    FROM t_postmeta t
    WHERE post_id = ? AND meta_key LIKE '_field_%'
) t
GROUP BY rn

Convert mysql value into columns

1 Answers1