Mysql : return row as columns

Question

Every month, I collect data in a table called wp_reports. For instance, I collect social media stats, which gives a table like :

------------------------------------------
month | facebook | twitter | linkedin || <-column names
------------------------------------------
Jan 16 | 100 | 200 | 300              || <- row values
Feb 16 | 102 | 202 | 302

Which is great to follow the evolution of our KPI, month by month.

However, I could like now to display data specific to the last month. I have a library that generates a pie chart from an SQL request.

I would like the result of my request as :

-----------
label | value
-------------
Facebook | 102
Twitter | 202
LinkedIN | 302

This would make all of my page work.

The problem is that I can't manage to find the right query.

I have the following one that retrives column names as labels :

SELECT
      CASE
        WHEN column_name = 'twitter' THEN 'Twitter'
        WHEN column_name = 'linkedin' THEN 'LinkedIn' 
        ELSE 'Facebook'
      END
      as label
    FROM information_schema.columns 
    WHERE table_name='wp_report' AND column_name IN ('facebook', 'twitter', 'linkedin')

I would like now to know how to merge it with a column called "value" that would contain the matching number.

If I could spin the result of the following query as a column called "value", that'd be great :

'SELECT newsletter, facebook, twitter, gplus, linkedin FROM wp_report ORDER BY date DESC LIMIT 1'

Thank you for your help.

Cheers ! I used this one too : http://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows — Grégoire Llorca, Oct 20 '16 at 12:25

score 0 · Answer 1 · answered Oct 20 '16 at 12:20

You can use following query:-

select 'Facebook' label,facebook value from wp_reports where month='JAN'
UNION ALL
select 'Twitter' label,Twitter value from wp_reports where month='JAN'
UNION ALL
select 'LinkedIn' label,LinkedIn value from wp_reports where month='JAN';

Mysql : return row as columns

1 Answers1