在R语言中使用sqldf：如何将SUB1-SUB5列转换为行

当然可以将SUB1到SUB5的列转换为行形式，以下提供两种常用方法：

方法一：在sqldf的SQL语句中直接实现 #

通过UNION ALL将每个汇总列拆分为单独的行，最终输出按年份和科目排序的结果：

sqldf("
SELECT YEAR, 'SUB1' AS Subject, SUM(SUB1) AS Value FROM table1 GROUP BY YEAR
UNION ALL
SELECT YEAR, 'SUB2' AS Subject, SUM(SUB2) AS Value FROM table1 GROUP BY YEAR
UNION ALL
SELECT YEAR, 'SUB3' AS Subject, SUM(SUB3) AS Value FROM table1 GROUP BY YEAR
UNION ALL
SELECT YEAR, 'SUB4' AS Subject, SUM(SUB4) AS Value FROM table1 GROUP BY YEAR
UNION ALL
SELECT YEAR, 'SUB5' AS Subject, SUM(SUB5) AS Value FROM table1 GROUP BY YEAR
ORDER BY YEAR, Subject
")

执行后会得到三列：YEAR（年份）、Subject（科目，即SUB1-SUB5）、Value（对应科目的汇总值）。

方法二：先获取宽表再用tidyr转换 #

如果已经得到了原查询的宽表结果，可以用tidyr包的pivot_longer函数将宽表转为长表：

library(sqldf)
library(tidyr)

# 执行原查询得到宽表
wide_result <- sqldf("
      SELECT YEAR, SUM(SUB1) AS 'SUB1', SUM(SUB2) AS 'SUB2',
      SUM(SUB3) AS 'SUB3', SUM(SUB4) AS 'SUB4', SUM(SUB5) AS 'SUB5'
      FROM  table1
      GROUP BY YEAR
      ")

# 转换为行形式的长表
long_result <- wide_result %>%
  pivot_longer(
    cols = starts_with("SUB"),  # 指定要转换的列（所有以SUB开头的列）
    names_to = "Subject",       # 新列名，存储原列的名称
    values_to = "Value"         # 新列名，存储原列的汇总值
  )

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