[Solved] How can I pull data from specific rows via SQL and separate them into their own columns?

EverSQL Database Performance Knowledge Base

How can I pull data from specific rows via SQL and separate them into their own columns?

I apologize if I cannot explain this properly in advance but I will do my best.

I have a SQL table. In the table, there are multiple columns. One of which is labeled 'meta_key' and another which is 'meta_value'. I would like to know how to write SQL that will pull specific rows in one column and also gather the data from another column in the same row and separate all of these out into their own columns. (Please forgive me if this doesn't make sense.)

I've tried the following which was provided by a friend:

SELECT meta_key, meta_value
FROM wp_gf_entry_meta
WHERE meta_key = 3 OR meta_key = 9 OR meta_key = 22 OR meta_key = 23

But it only gets me halfway there in that it provides all of the data from those rows and columns and consolidates it all into a single column.

Again, the SQL that was tried was:

SELECT meta_key, meta_value
FROM wp_gf_entry_meta
WHERE meta_key = 3 OR meta_key = 9 OR meta_key = 22 OR meta_key = 23

I didn't receive error messages for above but it did not provide the desired outcome.

Here is a screenshot of the table before the above SQL:

Here is a screenshot of the table before the above SQL:

and afterd

And here is a screenshot after:

UPDATE

I've made some progress. I believe what they call an 'Aggregate Function' is what I can use.

This has gotten me closer to my goal:

  SELECT 
  max(case when meta_key = 3 then meta_value end) Ticker,
  max(case when meta_key = 9 then meta_value end) Reason,
  max(case when meta_key = 22 then meta_value end) Resistance,
  max(case when meta_key = 23 then meta_value end) Support
  from wp_gf_entry_meta

The only problem now is that it only returns one row and I'm not certain how to make it return more than one row.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Prefer IN Clause Over OR Conditions (modified query below): Using an IN clause is far more efficient than OR conditions, when comparing a column to more than one optional values. When using an IN clause, the database sorts the list of values and uses a quick binary search.
Optimal indexes for this query:
ALTER TABLE `wp_gf_entry_meta` ADD INDEX `wp_entry_idx_meta_key` (`meta_key`);
The optimized query:
SELECT
        wp_gf_entry_meta.meta_key,
        wp_gf_entry_meta.meta_value 
    FROM
        wp_gf_entry_meta 
    WHERE
        wp_gf_entry_meta.meta_key IN (
            3, 9, 22, 23
        )

Related Articles



* original question posted on StackOverflow here.