在SQL中尝试透视表时出现未知语法错误

Question

我正在尝试转换一个数据表--我希望所有的行都变成列，所有的列变成行，有效地交换它们。我正在使用this answer中的方法来完成此操作。

如果有什么不同的话，那就是我正在使用PHPMyAdmin在树莓派(我的数据非常少)上运行我自己的MariaDB服务器。

表(截断)如下所示：

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

我试着把它转换成这样：

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

我的问题是：

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');

当我运行它的时候，我得到了这个错误：

#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

我不知道我的代码在做什么，也不知道“表t1”交叉连接和"c“和"q”子查询的真正含义，所以我不知道如何修复这个错误。

附注:我在大型"CASE Coach“语句中的新列名以反引号`表示，因为其中一个名称必须是Grant，但那是一个SQL关键字。有人告诉我这就是解决这个问题的方法。

Answer 1

从c中删除最后一个UNION ALL，如下所示；

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'
 ) 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');

有关UNION ALL的文档是here