如何修改MySQL数据查询逻辑实现多服务类型列转单列行输出

你可以使用UNION ALL实现多列拆分为多行的需求，该写法兼容所有MySQL版本，替换实际表名后即可直接运行：

SELECT 
  Family AS family,
  Members AS Member,
  `type of service 1` AS `Type of service`
FROM 你的实际表名
UNION ALL
SELECT 
  Family AS family,
  Members AS Member,
  `Type of Service 2` AS `Type of service`
FROM 你的实际表名
UNION ALL
SELECT 
  Family AS family,
  Members AS Member,
  `Type of service 3` AS `Type of service`
FROM 你的实际表名
-- 可选：按家庭、成员排序输出
ORDER BY family, Member;

如果你使用的是MySQL 8.0及以上版本，也可以用更简洁的UNPIVOT语法实现：

SELECT 
  Family AS family,
  Members AS Member,
  service_type AS `Type of service`
FROM 你的实际表名
UNPIVOT (
  service_type FOR service_column IN (
    `type of service 1`, 
    `Type of Service 2`, 
    `Type of service 3`
  )
) AS unpivoted_result;

注意：原表的服务类型列名包含空格，所以需要用反引号包裹避免语法报错。

内容的提问来源于stack exchange，提问作者Abood Jayyousi