I am trying to transform a table of data–I want the rows to become the columns, and the columns to become the rows (like a total complete pivot). I am using the method from this answer to do so.
If it makes any difference, I am running my own SQL server on a Raspberry Pi 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');
In part of the code I need to write WHEN ‘Grant’ THEN Grant but that second unquoted Grant becomes a keyword and messes with my code. When I put Grant in single quotes it throws me a syntax error further down where I write ‘) c’ but I suspect my little cheat probably has something to do with it. What shall I do?
Advertisement
Answer
You seem to be suggesting that Grant
is a column name — bad choice, but you are stuck with it. The standard way to escape names is to use double quotes:
WHEN 'Grant' THEN "Grant"
Some databases use backticks or square braces instead:
WHEN 'Grant' THEN `Grant` WHEN 'Grant' THEN [Grant]