sql - 重命名 SQL 查询转置中的列
问题描述
我有一个 SQL 查询,而不是按列转置行,但我不知道如何重命名列。
select *
from
(select CustomerID, FiscalPeriod, SaleAmtLocalCurr
from PerfTrk.dbo.IRIS_SaleFact
where CountryCode = '00001'
and DivisionCode = 'INS'
and SaleAmtLocalCurr > 0
and datefromparts(FiscalYear, FiscalPeriod, 1) between datefromparts(2019,9, 1) and datefromparts(2020,8, 31)) d
pivot
(sum(SaleAmtLocalCurr) for FiscalPeriod in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) piv;
结果是
CustomerID 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
我想用月份名称重命名列
谢谢
解决方案
CASE
始终是一种选择
select
*
from
(select
CustomerID,
(CASE
WHEN FiscalPeriod = 1 THEN 'JAN'
WHEN FiscalPeriod = 2 THEN 'FEB'
WHEN FiscalPeriod = 3 THEN 'MAR'
WHEN FiscalPeriod = 4 THEN 'APR'
WHEN FiscalPeriod = 5 THEN 'MAY'
WHEN FiscalPeriod = 6 THEN 'JUN'
WHEN FiscalPeriod = 7 THEN 'JUL'
WHEN FiscalPeriod = 8 THEN 'AUG'
WHEN FiscalPeriod = 9 THEN 'SEP'
WHEN FiscalPeriod = 10 THEN 'OCT'
WHEN FiscalPeriod = 11 THEN 'NOV'
WHEN FiscalPeriod = 12 THEN 'DEC'
END) AS 'Month',
SaleAmtLocalCurr
from
PerfTrk.dbo.IRIS_SaleFact
where
CountryCode = '00001'
and DivisionCode = 'INS'
and SaleAmtLocalCurr > 0
and datefromparts(FiscalYear, FiscalPeriod, 1)
between datefromparts(2019,9, 1) and datefromparts(2020,8, 31)) d
pivot
(sum(SaleAmtLocalCurr) for FiscalPeriod in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) piv;
推荐阅读
- python-3.x - 如何从 np.where() 而不是 True/False 获取单元格的值
- r - ggplot2 图例标题不尊重 Google 字体的粗体
- reactjs - 如何在 Ant Design Form 中使用 async onFinish 方法
- firebase - Firebase SwiftUI and Firebase Auth - not reading user ID?
- gradle - Gradle 如何自动更新依赖的版本?
- scala - Spark - 从数组对象中选择多列
- debugging - 通过 UART 将缓冲区作为 USB 数据发送到 VCP 时无法访问内存位置
- python - 为什么硬编码的 url 有效,但 Django 中的名称空间 url 无效?
- java - 如何获取可启动应用程序的列表?
- scala - scala:从变量列列表中获取与最大列值对应的列名