asked on

MySQL how to transpose a single row of 15 cols to 15 rows with an order by

How can I turn a single row of 15 columns into 15 rows with 2 columns for ORDER BY?

I have an online form and the SQL to score the form data we have puts everything in a single row. What we need is 15 rows so we can use ORDER BY. I have googled PIVOT mySQL, but that is the opposite of what I want.

What I am doing is summing multiple rows per column from a ninja form:

SELECT  SUM(ACCEPTANCE) as ACCEPTANCE, 
        SUM(ASSOCIATION) as ASSOCIATION, 
        SUM(CURIOSITY) as CURIOSITY,
        SUM(EGO) as EGO, 
        SUM(EMPATHY) as EMPATHY, 
        SUM(ENVY) as ENVY, 
        SUM(EXCITEMENT) as EXCITEMENT, 
        SUM(FAMILY) as FAMILY, 
        SUM(FEAR) as FEAR, 
        SUM(GREED) as GREED, 
        SUM(HUMOR) as HUMOR, 
        SUM(HUNGER) as HUNGER, 
        SUM(LAZY) as LAZY, 
        SUM(SEX) as SEX, 
        SUM(TRUST) as TRUST

FROM v_nf_questions q
JOIN v_nf_answers a on q.q_id=a.a_id
JOIN answers_table ans ON a.f_answer=ans.ARCH_NAME
WHERE nform_id = 348

Open in new window

So here is what I get

But here is what I want

what_i_have.jpeg
what_i_want.jpeg

Julian Hansen

You can try a Union - ugly but it should work

SELECT  SUM(ACCEPTANCE) as ACCEPTANCE
FROM v_nf_questions q
JOIN v_nf_answers a on q.q_id=a.a_id
JOIN answers_table ans ON a.f_answer=ans.ARCH_NAME
WHERE nform_id = 348
UNION
SELECT SUM(ASSOCIATION) as ASSOCIATION
FROM v_nf_questions q
JOIN v_nf_answers a on q.q_id=a.a_id
JOIN answers_table ans ON a.f_answer=ans.ARCH_NAME
WHERE nform_id = 348
UNION
...

Open in new window

You can then do a query against this to do the order by.

It might be simpler to do it in script though - what are you using to access the query?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Sorry I edited the answer above. If you used the prior versions it may not work as expected.

I am going to suggest that you really should consider applying this "unpivot" from columns to rows as the way you store your answers. SQL is "row oriented" and (as you can see in the previous answers) that when you have data spread out over columns like you have, queries get complex perhaps even very complex.

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

nb Common table expressions in MySQL starts with version 8 (not yet in common use) i.e.

with cte as (... )
select ...
from cte

might not work in your MySQL version.

johnsone

Not a heavy user of MySQL, it was in the documentation, so I thought it was available.

PortletPaul

@johnsone :) no problem

I mentioned it because many MySQL users aren't that aware of the new version and the new features it includes.

ps:
I still prefer to stick with derived tables when cte's aren't absolutely needed.
You are right about UNPIVOT, it does not exist (so far) in MySQl even with v8

David Favor

While you can achieve what you're after using above suggestions... if your dataset grows large, you'll require LIMIT statements else you may end up with massive CPU churn to accomplish what you're after.

Be sure to check resource use at your server level for your project to ensure your data is stored to provide efficient retrieval.

sqlagent007

ASKER

I think I am running V8, but I am testing your other query now...

sqlagent007

ASKER

Thanks experts!!!