SQL Query to Create Pivot View

Get Help
, ,
1

I’ve set up a particular form for which I need to be able to query the responses in the database directly so that these responses can be managed / edited. However, the way that the values are stored in the database is “vertically” in the meta_value column of the wp_gf_entry_meta table, which looks like, for example

|  id | form_id | entry_id | meta_key | meta_value |
---------------------------------------------------
| 1234 |   52  |   1234   |  1.3     | Fred
| 1235 |   52  |   1234   |  1.6     | Flintstone
| 1236 |   52  |   1234   |  3       | fred@bedrock.com
| 1237 |   52  |   1234   |  4.3     | Pebbles
| 1237 |   52  |   1234   |  4.6     | Flintstone

I can relate the meta_keys to the questions in the form such that I know that 1.3 = Parent First Name, for example. I’m not a MYSQL expert by any means, but I know there must be a way to generate a editable pivot table or view that looks like the following with the rows transposed into columns with the meta_keys becoming column headers:

form_id | entry_id |  1.3  | 1.6        |  3               |  4.3    |  4.6
-----------------------------------------------------------------------------
52      |   1234   |  Fred | Flintstone | fred@bedrock.com | Pebbles | Flintstone

This is basically exactly how the entries look in the Wordpress admin for the form, I just have no idea how to achieve the same with MYSQL query. Ideally the solution would create a VIEW so that the data can be manipulated.

Many many thanks…

Trevor

2

Hello Trev. Welcome!

First, I recommend NOT using MySQL queries directly and instead using Gravity Forms API functions from the GFAPI class. It handles a lot of the low-level formatting stuff for you:

For the task at hand specifically, I recommend taking a look at GravityView. Their whole existence is built on display data collected by Gravity Forms:

They have a datatables plugin as well. There is a demo of the functionality here:

If you have any other questions, please let us know.

3

Hi Chris, many thanks for pointing me in the right direction. I’ll certainly look into those references.

1 Like