I am trying to transform a table of data--I want ALL of the rows to become the columns, and ALL of the columns to become the rows, effectively swapping them. I am using the method from this answer to do so.
If it makes any difference, I am running my own MariaDB server on a Raspberry Pi (I have very little data) using PHPMyAdmin.
The table (truncated) looks like this:
Personality|Abby|Aidan|Brandon|Bobby
Agreeabl...|93 |65 |74 |68
Compassion |95 |65 |96 |96
Politeness |81 |9 |21 |12
Conscient..|45 |13 |99 |28
I'm trying to transpose it into this:
Coach |Agreeableness|Compassion|Politeness|Conscientiousness
Abby |93 |95 |81 |45
Aidan |65 |96 |9 |13
Brandon|74 |96 |21 |99
Bobby |68 |96 |12 |28
And my query is here:
SELECT Coach,
MAX(CASE WHEN Personality = 'Agreeableness' THEN value END) `Agreeableness`,
MAX(CASE WHEN Personality = 'Compassion' THEN value END) `Compassion`,
MAX(CASE WHEN Personality = 'Politeness' THEN value END) `Politeness`,
MAX(CASE WHEN Personality = 'Conscientiousness' THEN value END) `Conscientiousness`,
MAX(CASE WHEN Personality = 'Industriousness' THEN value END) `Industriousness`,
MAX(CASE WHEN Personality = 'Orderliness' THEN value END) `Orderliness`,
MAX(CASE WHEN Personality = 'Extraversion' THEN value END) `Extraversion`,
MAX(CASE WHEN Personality = 'Enthusiasm' THEN value END) `Enthusiasm`,
MAX(CASE WHEN Personality = 'Assertiveness' THEN value END) `Assertiveness`,
MAX(CASE WHEN Personality = 'Neuroticism' THEN value END) `Neuroticism`,
MAX(CASE WHEN Personality = 'Withdrawal' THEN value END) `Withdrawal`,
MAX(CASE WHEN Personality = 'Volatility' THEN value END) `Volatility`,
MAX(CASE WHEN Personality = 'Openness' THEN value END) `Openness`,
MAX(CASE WHEN Personality = 'Intellect' THEN value END) `Intellect`,
MAX(CASE WHEN Personality = 'Openness (Aspect)' THEN value END) `Openness (Aspect)`
FROM
(
SELECT Personality, Coach,
CASE Coach
WHEN 'Abby' THEN Abby
WHEN 'Aidan' THEN Aidan
WHEN 'Brandon' THEN Brandon
WHEN 'Bobby' THEN Bobby
WHEN 'Carlos' THEN Carlos
WHEN 'Carrie' THEN Carrie
WHEN 'Chassidy' THEN Chassidy
WHEN 'Emily' THEN Emily
WHEN 'Galen' THEN Galen
WHEN 'Gavin' THEN Gavin
**WHEN 'Grant' THEN Grant** #part of interest
WHEN 'Greg' THEN Greg
WHEN 'Jack' THEN Jack
WHEN 'Jenn' THEN Jenn
WHEN 'Noah' THEN Noah
WHEN 'Mae' THEN Mae
WHEN 'Patrick' THEN Patrick
WHEN 'Titus' THEN Titus
END value
FROM table1 t CROSS JOIN
(
SELECT 'Abby' Coach UNION ALL
SELECT 'Aidan' UNION ALL
SELECT 'Brandon' UNION ALL
SELECT 'Bobby' UNION ALL
SELECT 'Carlos' UNION ALL
SELECT 'Carrie' UNION ALL
SELECT 'Chassidy' UNION ALL
SELECT 'Emily' UNION ALL
SELECT 'Galen' UNION ALL
SELECT 'Gavin' UNION ALL
SELECT 'Grant' UNION ALL
SELECT 'Greg' UNION ALL
SELECT 'Jack' UNION ALL
SELECT 'Jenn' UNION ALL
SELECT 'Noah' UNION ALL
SELECT 'Mae' UNION ALL
SELECT 'Patrick' UNION ALL
SELECT 'Titus' UNION ALL
) c
) q
GROUP BY Coach
ORDER BY FIELD(Coach, 'Abby', 'Aidan', 'Brandon', 'Bobby', 'Carlos', 'Carrie', 'Chassidy', 'Emily', 'Galen', 'Gavin', 'Grant', 'Greg', 'Jack', 'Jenn', 'Noah', 'Mae', 'Patrick', 'Titus');
I am getting this error while running it:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') c
) q
GROUP BY Coach
ORDER BY FIELD(Coach, 'Abby', 'Aidan', 'Bran...' at line 60
I do not really know what my code is doing, and what the "table t1" cross join and "c" and "q" subqueries really mean, so I don't know how to fix the error.
P.S. My new column names in the large "CASE Coach" statement are in backticks ` because one of the names has to be Grant but that's a SQL keyword. I've been told that's how to fix that problem.
