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;

函数
SUBSTRING_INDEX

-- 求GROUP_CONCAT的元素个数 把,替换为空然后做减法
length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT(questionitem.content),',','')) + 1 AS OPTION_LEN

-- 获取指定的第几个元素,先获取第三个逗号左侧全部字符串,在获取右侧第一个逗号右侧全部字符串
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(questionitem.content), ',', 3), ',', -1) as '获取到第三个逗号的记录的最后一个'

参考:
https://blog.csdn.net/weixin_45716993/article/details/103234724