SQLite中如何仅列一次列名，查询每行最大值对应的列名？

需求：针对数据表的每一行，选出值最大的列的列名，要求SQL查询中列名仅出现一次。

Oracle中可通过unpivot语法实现，示例代码如下：

select objectid, max(col_name) keep (dense_rank first order by col_val desc) max_col_name
from employment_by_industry
unpivot (
  col_val
  for col_name in (
    agr_forest_fish, mining_quarry, mfg, electric, water_sew  --cols only listed once
  )
)
group by objectid

在SQLite中没有原生unpivot语法，但可以借助JSON函数实现列转行，同时保证列名仅出现一次，最简洁写法如下：

SELECT 
  objectid,
  (SELECT json_extract(value, '$.key')
   FROM json_each(json_object(
     'agr_forest_fish', agr_forest_fish,
     'mining_quarry', mining_quarry,
     'mfg', mfg,
     'electric', electric,
     'water_sew', water_sew
   ))
   ORDER BY json_extract(value, '$.value') DESC, json_extract(value, '$.key') ASC
   LIMIT 1) AS max_col_name
FROM employment_by_industry;

说明： #

• 用json_object将每行目标列转换为JSON对象，列名仅需书写一次，同时绑定对应列值；
• 通过json_each将JSON对象拆分为多行键值对；
• 子查询按列值降序排序，取第一行的键（即原列名），若存在多列值相同的情况，可通过列名升序确定优先级；
• 最终得到每行中值最大的列名。

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