sql - 日期范围内的 SQL PIVOT
问题描述
我正在尝试旋转此日期列表以用作列标题。示例查询已简化为 12 个日历月注意:实际日期范围最终总共为 240 个月。我不太了解 PIVOT 功能
DROP TABLE if exists #nbrs
DROP TABLE if exists #nbr
DECLARE @StartDate DATE = '20210101'
DECLARE @EndDate DATE = '20211201'
SELECT DATEADD(MONTH, nbr - 1, @StartDate) AS DateRange
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(MONTH, @StartDate, @EndDate)
当前输出:
日期范围 |
---|
2021-01-01 |
2021-02-01 |
2021-03-01 |
2021-04-01 |
2021-05-01 |
2021-06-01 |
2021-07-01 |
2021-08-01 |
2021-09-01 |
2021-10-01 |
2021-11-01 |
2021-12-01 |
期望的输出:
2021-01-01 | 2021-02-01 | 2021-03-01 |
---|---|---|
解决方案
您可以通过使用动态 sql 查询和数据透视函数来实现所需的输出。除了日期列之外,我还为枢轴函数添加了另一个虚拟列。
DECLARE @StartDate DATE = '20210101';
DECLARE @EndDate DATE = '20211201';
DECLARE @DynamicQuery AS NVARCHAR(MAX);
DECLARE @ColumnList AS NVARCHAR(MAX);
-- populate column list
WITH DateRange(DateData) AS
(
SELECT @StartDate as Date
UNION ALL
SELECT DATEADD(MONTH,1,DateData)
FROM DateRange
WHERE DateData < @EndDate
)
SELECT @ColumnList = ISNULL(@ColumnList + ',', '')
+ QUOTENAME(DateData)
FROM DateRange
OPTION (MAXRECURSION 300);
-- Construct dynamic query
SELECT @DynamicQuery = N' WITH DateRange(DateData, AnotherColumn) AS
(
SELECT CAST(''' + CAST(@StartDate AS NVARCHAR) + ''' as Date) as DateData, 1 as AnotherColumn
UNION ALL
SELECT DATEADD(MONTH,1,DateData), 1 as AnotherColumn
FROM DateRange
WHERE DateData < ''' + CAST(@EndDate AS NVARCHAR) + '''
)
SELECT ' + @ColumnList + ' FROM (
SELECT * FROM DateRange
) As SouceTable PIVOT (MAX(AnotherColumn) FOR DateData IN ( ' +
@ColumnList + ')) AS PivotTable
OPTION (MAXRECURSION 300)';
-- run @DynamicQuery;
EXEC sp_executesql @DynamicQuery;
结果:
推荐阅读
- github - What is Best practice to Move DATA FILES to GITHUB Repo
- mysql - Set global settings configuration instead of use ANY function on ClickHouse
- opencv - OpenCV CUDA SURF Performance vs CPU version
- azure - 使用带有 SharedAccessKey 的 Rest API 列出 Azure 服务总线的队列/主题
- node.js - Elastic Search 多匹配查询不能忽略特殊字符
- html - 更新离子模式中的列表会更新原始列表 ionic 5
- python-3.x - 'For-loop' 的值
- php - 何时调用 Eloquent 模型的 booted() 方法?
- python - 组合熊猫数据框中给定索引列表的行数据
- python - 使用 Python 从城市名称中获取时间