Oracle年月分列销售数据SQL列转行（宽表转长表）方案问询

数据库侧转换方案（推荐优先使用） #

1. UNION ALL 拼接方案（全Oracle版本兼容） #

逻辑为将12个月份字段分别拆分为独立查询后合并，兼容性最高：

SELECT 
  Product,
  Year,
  'Jan' AS Month,
  Jan AS Sale
FROM sales_wide -- 替换为你的宽表名
WHERE Jan IS NOT NULL -- 可选，过滤无销售额的空记录

UNION ALL
SELECT Product, Year, 'Feb' AS Month, Feb AS Sale FROM sales_wide WHERE Feb IS NOT NULL
UNION ALL
SELECT Product, Year, 'Mar' AS Month, Mar AS Sale FROM sales_wide WHERE Mar IS NOT NULL
UNION ALL
SELECT Product, Year, 'Apr' AS Month, Apr AS Sale FROM sales_wide WHERE Apr IS NOT NULL
UNION ALL
SELECT Product, Year, 'May' AS Month, May AS Sale FROM sales_wide WHERE May IS NOT NULL
UNION ALL
SELECT Product, Year, 'Jun' AS Month, Jun AS Sale FROM sales_wide WHERE Jun IS NOT NULL
UNION ALL
SELECT Product, Year, 'Jul' AS Month, Jul AS Sale FROM sales_wide WHERE Jul IS NOT NULL
UNION ALL
SELECT Product, Year, 'Aug' AS Month, Aug AS Sale FROM sales_wide WHERE Aug IS NOT NULL
UNION ALL
SELECT Product, Year, 'Sep' AS Month, Sep AS Sale FROM sales_wide WHERE Sep IS NOT NULL
UNION ALL
SELECT Product, Year, 'Oct' AS Month, Oct AS Sale FROM sales_wide WHERE Oct IS NOT NULL
UNION ALL
SELECT Product, Year, 'Nov' AS Month, Nov AS Sale FROM sales_wide WHERE Nov IS NOT NULL
UNION ALL
SELECT Product, Year, 'Dec' AS Month, Dec AS Sale FROM sales_wide WHERE Dec IS NOT NULL

优点：所有Oracle版本都支持，逻辑直观易排查；缺点：表会被扫描12次，超大数据量下性能偏低。

2. UNPIVOT 行转列函数方案（Oracle 11g+支持） #

Oracle 11g新增的原生行转列语法，仅扫描一次表，性能更优：

SELECT 
  Product,
  Year,
  Month,
  Sale
FROM sales_wide -- 替换为你的宽表名
UNPIVOT (
  Sale FOR Month IN (
    Jan AS 'Jan',
    Feb AS 'Feb',
    Mar AS 'Mar',
    Apr AS 'Apr',
    May AS 'May',
    Jun AS 'Jun',
    Jul AS 'Jul',
    Aug AS 'Aug',
    Sep AS 'Sep',
    Oct AS 'Oct',
    Nov AS 'Nov',
    Dec AS 'Dec'
  )
)
-- 可额外添加WHERE条件过滤数据

优点：语法简洁，执行效率高；缺点：仅支持11g及以上版本Oracle。

Power BI侧转换方案（无需修改数据库查询时使用） #

1. Power Query 逆透视操作（可视化操作，推荐） #

• 将宽表数据导入Power BI后，进入「Power Query编辑器」
• 按住Ctrl选中Product、Year两个固定字段
• 右键点击选中字段，选择「逆透视其他列」
• 将生成的属性列重命名为Month，值列重命名为Sale即可
  优点：零代码操作，调整灵活；缺点：超大数据量下转换速度慢于数据库侧转换。

2. DAX 表达式生成长表 #

如果需要保留原始宽表同时生成长表，可使用DAX的UNION+SELECTCOLUMNS组合：

销售长表 = 
UNION(
  SELECTCOLUMNS('销售宽表', "Product", '销售宽表'[Product], "Year", '销售宽表'[Year], "Month", "Jan", "Sale", '销售宽表'[Jan]),
  SELECTCOLUMNS('销售宽表', "Product", '销售宽表'[Product], "Year", '销售宽表'[Year], "Month", "Feb", "Sale", '销售宽表'[Feb]),
  -- 依次补充剩余10个月份的逻辑
  SELECTCOLUMNS('销售宽表', "Product", '销售宽表'[Product], "Year", '销售宽表'[Year], "Month", "Dec", "Sale", '销售宽表'[Dec])
)

优点：可同时保留宽表、长表两份数据；缺点：大表下性能较差。

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