时间戳

首页 > 解决方案 > 带有转换日期的 SQL Server PIVOT

tsql - 带有转换日期的 SQL Server PIVOT

问题描述

我在 SQL Server 中使用 PIVOT 时遇到了困难。

我的表有以下数据：

    saleDateTime        |  Value   |
------------------------+----------|
2018-10-30 14:29:01.000 |    87    |
2018-10-30 16:07:22.000 |    50    |
2018-10-30 16:19:36.000 |    80    |
2018-10-30 17:08:04.000 |    20    |
2018-10-30 18:09:16.000 |    35    |
2018-10-30 19:09:24.000 |    90    |
2018-10-31 13:09:41.000 |    12    |
2018-10-31 14:09:54.000 |    80    |
2018-10-31 16:18:12.000 |    30    |

我需要的是以下结果：

  saleDate    |  13   |  14   |  16   |  17  |  18  |  19  |
--------------+-------+-------+-------+------+------+------|
2018-10-30    |   0   |   87  |  130  |  20  |  35  |  90  |
2018-10-31    |   12  |   80  |  30   |  0   |  0   |  0   |

我需要带有销售日期的行，带有销售时间的列标题以及行的值是该日期和小时内的销售总和。

我无法绕过 PIVOT 函数来解决这种情况。我的表只有DateTime值，我需要从该列中获取两件事：分别是日期和时间。有没有办法使用PIVOTaCONVERT来完成这项工作？

提前致谢！

标签： tsqlsql-server-2008

解决方案

以下是如何使用PIVOT来获得所需的结果 - 转换salesDateTime为date删除时间部分，并按小时旋转，计算为DATEPART(hour, saleDateTime)：

declare @Sales table(saleDateTime datetime, Value int)

insert into @Sales (saleDateTime, Value) values
('2018-10-30 14:29:01.000', 87),
('2018-10-30 16:07:22.000', 50),
('2018-10-30 16:19:36.000', 80),
('2018-10-30 17:08:04.000', 20),
('2018-10-30 18:09:16.000', 35),
('2018-10-30 19:09:24.000', 90),
('2018-10-31 13:09:41.000', 12),
('2018-10-31 14:09:54.000', 80),
('2018-10-31 16:18:12.000', 30)


SELECT
    saleDate
    , ISNULL([0], 0) as [0]
    , ISNULL([1], 0) as [1]
    , ISNULL([2], 0) as [2]
    , ISNULL([3], 0) as [3]
    , ISNULL([4], 0) as [4]
    , ISNULL([5], 0) as [5]
    , ISNULL([6], 0) as [6]
    , ISNULL([7], 0) as [7]
    , ISNULL([8], 0) as [8]
    , ISNULL([9], 0) as [9]
    , ISNULL([10], 0) as [10]
    , ISNULL([11], 0) as [11]
    , ISNULL([12], 0) as [12]
    , ISNULL([13], 0) as [13]
    , ISNULL([14], 0) as [14]
    , ISNULL([15], 0) as [15]
    , ISNULL([16], 0) as [16]
    , ISNULL([17], 0) as [17]
    , ISNULL([18], 0) as [18]
    , ISNULL([19], 0) as [19]
    , ISNULL([20], 0) as [20]
    , ISNULL([21], 0) as [21]
    , ISNULL([22], 0) as [22]
    , ISNULL([23], 0) as [23]
FROM  
(SELECT cast(saleDateTime as DATE) as saleDate, Value, DATEPART(hour, saleDateTime) as saleHour FROM @Sales) AS SourceTable  
PIVOT  
(  
    SUM(Value)  
    FOR saleHour IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
    [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])  
) AS PivotTable;

我添加了所有时间，但如有必要，您可以删除不需要的时间段。

推荐阅读