Horizontal to vertical table

All we need is an easy explanation of the problem, so here it is.

I have this query

SELECT quiz_id, COUNT(case when options LIKE '%question_id_8":"1%' then 1 else null end) as 'answered 1',
COUNT(case when options LIKE '%question_id_8":"2%' then 1 else null end) as 'answered 2',
COUNT(case when options LIKE '%question_id_8":"3%' then 1 else null end) as 'answered 3',
COUNT(case when options LIKE '%question_id_8":"4%' then 1 else null end) as 'answered 4',
COUNT(case when options LIKE '%question_id_8":"5%' then 1 else null end) as 'answered 5',
COUNT(case when options LIKE '%question_id_8":"6%' then 1 else null end) as 'answered 6',
COUNT(case when options LIKE '%question_id_8":"7%' then 1 else null end) as 'answered 7',
COUNT(case when options LIKE '%question_id_8":"8%' then 1 else null end) as 'answered 8'
FROM wp_aysquiz_reports
WHERE quiz_id = 2;

and I have this result

Horizontal to vertical table

How can I make it like this:

Answered 1    7
Answered 2    3
Answered 3    1
Answered 4    1
Answered 5    1
Answered 6    1
Answered 7    1
Answered 8    0

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

What you’ve done in the original query is considered pivoting, you could either apply an unpivot afterwards or just not do the pivoting.

The special thing about your data seems like options could potentially have multiple matching like conditions, so you could union all each version and aggregate that:

select quiz_id, question, count(*) 
from (
          SELECT quiz_id, case when options LIKE '%question_id_8":"1%' then 'answered 1' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"2%' then 'answered 2' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"3%' then 'answered 3' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"4%' then 'answered 4' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"5%' then 'answered 5' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"6%' then 'answered 6' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"7%' then 'answered 7' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"8%' then 'answered 8' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
)
where question is not null
group by quiz_id,question

Note I’ve included a group by condition, this is because you want a row per quiz_id, question and you don’t just want MySQL to give you an arbitrary value (see MySQL Handling of GROUP BY).

Instead of using a case when expression in each of the selects, you could just use a filter where options LIKE '%question_id_8":"1%' etc. This could be faster, you would also not require the where question is not null filter at the end. I preferred rewriting as I did because it’s easier to follow from your original statement.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply