如何转置表格并对结果进行分组？

Question

我有一个如下的表格：

datetime                   datacenter   machines
---------------------------------------------------------
2020-05-13 12:00:00.000    DC01         500
2020-05-13 12:00:00.000    DC02         100  
2020-04-10 13:00:00.000    DC01         510
2020-04-10 13:00:00.000    DC02         120
2020-03-1 14:00:00.000     DC01         530
2020-03-1 14:00:00.000     DC02         140

时间戳列为 Datetime2 类型，其余为 VARCHAR。需要创建一个新视图，按时间戳分组，并从具有 dc01 和 dc02 数据中心行中提取列，将各自行的机器数量放入相应的列中，最终对数据进行转置和合并。

在源表中，每个时间戳会有两条记录，一条记录来自于一个数据中心。当数据合并后，时间戳应该是唯一的。以下是所得到的 view 的示例：

resulting_view

datetime                   dc01_machines   dc02_machines
---------------------------------------------------------
2020-05-13 12:00:00.000    500             100 
2020-04-10 13:00:00.000    510             120
2020-03-1 14:00:00.000     530             140

我花了一些时间来寻找解决方案。

在我看来，解决方案是进行两个单独的查询，一个用于每个数据中心，用 UNION 将它们组合起来，然后只需按日期时间进行分组，但我肯定这很糟糕，并且它甚至不能运行，存在一个关于“GROUP”附近无效语法的错误。这是尝试：

(SELECT t1.datetime
 ,t1.machines as dc01_machines
 ,'' as dc02_machines
FROM table1 t1
WHERE datacenter = 'DC01')
UNION
 (SELECT t1.datetime
 ,'' as dc01_machines
 ,t1.machines as dc02_machines
FROM table1 t1
WHERE datacenter = 'DC02')
GROUP BY datetime

谢谢，感激任何帮助！

- robliv

Answer 1

我建议使用条件聚合。

与供应商特定的“透视”语法不同，这是一种可移植的方法（即它适用于大多数数据库），通常表现至少与供应商特定的实现一样好或更好。

它还更加灵活 - 这对于您特定的用例没有区别，但在更复杂的情况下会有所不同，例如：列出ID为510或更高的DC01机器和ID为120或更低的DC02机器。

select 
    datetime,
    max(case when datacenter = 'DC01' then machines end) dc01_machines,
    max(case when datacenter = 'DC02' then machines end) dc02_machines
from mytable
group by datetime
order by datetime

- GMB

---

谢谢，我测试了一下，它完美地工作了。简单而优雅的解决方案。如果我可以问一下，您知道如何将另一列添加到此解决方案中，该列将计算“dc01_machines”和“dc02_machines”的总和吗？ - robliv

Answer 2

CREATE TABLE yourtable ([datetime] datetime, datacenter varchar(10),machines INT)
insert into yourtable ([datetime],datacenter,machines) VALUES 
('2020-05-13 12:00:00.000' ,   'DC01'   ,  500 ),
('2020-05-13 12:00:00.000' ,   'DC02'   ,     100  ),
('2020-04-10 13:00:00.000',    'DC01'    ,     510),
('2020-04-10 13:00:00.000',    'DC02'    ,     120),
('2020-03-1 14:00:00.000',     'DC01'   ,      530),
('2020-03-1 14:00:00.000',     'DC02'   ,      140)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.datacenter) 
            FROM yourtable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [datetime], ' + @cols + ' from 
            (
                select [datetime],datacenter,machines
                from yourtable
           ) x
            pivot 
            (
                 max(machines)
                for datacenter in (' + @cols + ')
            ) p '


execute(@query)

datetime                | DC01 | DC02
:---------------------- | ---: | ---:
2020-03-01 14:00:00.000 |  530 |  140
2020-04-10 13:00:00.000 |  510 |  120
2020-05-13 12:00:00.000 |  500 |  100

db<>fiddle here

db<>fiddle {{链接1:此处}}

- Amira Bedhiafi