时间戳

首页 > 解决方案 > MSSQL 2012 - 在表格、数据透视表中逐小时跟踪零件编号？

sql-server - MSSQL 2012 - 在表格、数据透视表中逐小时跟踪零件编号？

问题描述

我正在尝试跟踪 SQL 数据库中的库存变化。我有一个活动表，其中包含用于另一个应用程序的所有当前库存。一个机架最多可以有两个不同的部件号。我写了这个 union/sum 来获得按零件号分组的当前总数。我想做的是每小时运行一个存储过程（我可以做这部分），以将其记录在我可以绘制图表的布局中。我可以将所有零件编号作为列来制作日志表，但是如何将数据插入到该特定列？

  select partNumber, sum(quantity) AS qty from (
  select partNumber, quantity from Racks
  Union ALL
  select partNumberB AS partNumber, qtyB AS qty from Racks) as x group by partNumber

这将返回如下内容：

+------------+--------+
| partnumber |  qty   |
+------------+--------+
| part_0443  |  11004 |
| part_0467  |  21583 |
| part_0468  |  26545 |
| part_0527  |  47036 |
| part_0557  |  51479 |
| part_0558  |  64285 |
| part_0631  |   8205 |
| part_0659  | 195676 |
+------------+--------+

我想返回的是这样的东西，它会插入到日志表中：


+---------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|   datetime    | part_0443 | part_0467 | part_0468 | part_0527 | part_0557 | part_0558 | part_0631 | part_0659 | part_1234 |
+---------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4/17/20 9:00  | 11004     | null      |     26545 | 47036     | 51479     |     64285 |      8205 |    195676 | null      |
| 4/17/20 10:00 | 9770      | 85411     |     25311 | 45802     | null      |     63051 |      6971 |    194442 | 100       |
| 4/17/20 11:00 | 132       | 548       |       548 | null      | 3231      |     65498 |      5421 |     32198 | 6546      |
| 4/17/20 12:00 | null      | 123456    |      5421 | 321       | 654       |       321 |       654 |       231 | 654       |
+---------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

我看到的一个问题是，并非所有零件始终都在活动零件表中，这暗示了上面的空值。

感谢大家的任何帮助或建议。

标签： sql-servertsql

解决方案

试试这个动态的支点。

我在这里使用一个临时表只是为了测试，但你可以很容易地替换你的真实表。

CREATE TABLE #racks 
(
    EntryDate DATETIME
    ,PartNumber NVARCHAR(100)
    ,QTY BIGINT
)

INSERT INTO #racks
VALUES
('2020-04-17 09:00:00.000','part_0443',  11004 ),
('2020-04-17 09:00:00.000','part_0468',  26545 ),
('2020-04-17 09:00:00.000','part_0527',  47036 ),
('2020-04-17 09:00:00.000','part_0557',  51479 ),
('2020-04-17 09:00:00.000','part_0558',  64285 ),
('2020-04-17 09:00:00.000','part_0631',   8205 ),
('2020-04-17 09:00:00.000','part_0659', 195676 ),
('2020-04-17 10:00:00.000','part_0443',9770),
('2020-04-17 10:00:00.000','part_0467',85411),
('2020-04-17 10:00:00.000','part_0468',25311),
('2020-04-17 10:00:00.000','part_0527',45802),
('2020-04-17 10:00:00.000','part_0558',63051),
('2020-04-17 10:00:00.000','part_0631',6971),
('2020-04-17 10:00:00.000','part_0659',194442),
('2020-04-17 10:00:00.000','part_1234',100),
('2020-04-17 11:00:00.000','part_0443',132),
('2020-04-17 11:00:00.000','part_0467',548),
('2020-04-17 11:00:00.000','part_0468',548),
('2020-04-17 11:00:00.000','part_0557',3231),
('2020-04-17 11:00:00.000','part_0558',65498),
('2020-04-17 11:00:00.000','part_0631',5421),
('2020-04-17 11:00:00.000','part_0659',32198),
('2020-04-17 11:00:00.000','part_1234',6546),
('2020-04-17 12:00:00.000','part_0467',123456),
('2020-04-17 12:00:00.000','part_0468',5421),
('2020-04-17 12:00:00.000','part_0527',321),
('2020-04-17 12:00:00.000','part_0557',654),
('2020-04-17 12:00:00.000','part_0558',321),
('2020-04-17 12:00:00.000','part_0631',654),
('2020-04-17 12:00:00.000','part_0659',231),
('2020-04-17 12:00:00.000','part_1234',654)

DECLARE @sql NVARCHAR(MAX) = N''
DECLARE @cols NVARCHAR(MAX) = N''
SELECT @cols += ',' + QUOTENAME(PartNumber) from #racks GROUP BY PartNumber
SELECT @cols = STUFF(@cols, 1,1, '')

SELECT @sql = 'SELECT EntryDate,'
    +@cols
+ ' FROM
    (SELECT * FROM #racks) src 
 PIVOT
(
    SUM(QTY)
    FOR PartNumber IN (' + @cols + ')
) as pvt'

EXEC (@SQL)

推荐阅读