mysql行转列(列不固定)

使用场景：由于题目的选项按行存储在数据库中，并且选项数量不是固定的，如下图

使用sql语句如下

SELECT
  questionitem.questionId,
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 1, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 1), ',', -1), '') AS '1',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 2, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2), ',', -1), '') AS '2',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 3, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 3), ',', -1), '') AS '3',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 4, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 4), ',', -1), '') AS '4',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 5, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 5), ',', -1), '') AS '5',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 6, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 6), ',', -1), '') AS '6',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 7, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 7), ',', -1), '') AS '7',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 8, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 8), ',', -1), '') AS '8',
  IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 >= 9, 
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 9), ',', -1), '') AS '9'
FROM
  questionitem
GROUP BY
  questionitem.questionId;

查询结果如下

sql分析：

SELECT
  questionitem.questionId,
  GROUP_CONCAT(questionitem.content) as OPTION_LIST,
  length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 AS OPTION_LEN,
  SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2) as '获取到第二个逗号的记录',
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 2), ',', -1) as '获取到第二个逗号的记录的最后一个'
FROM
  questionitem
GROUP BY
  questionitem.questionId;

最后利用 if 语句拆分显示
if(expr1, expr2, expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。