How to transpose table and group the results in?

user_11496618 0
Viewed 50

I have a table like this:

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

The datetime column is a Datetime2 type, rest are VARCHAR. I need to make a new view that will group the entries by datetime and make columns from the rows that have dc01 and dc02 datacenters and put the no. of machines in the respective rows, essentially transposing and merging the data.

In the source table there will always be 2 entries for each datetime, one for each datacenter, and when it is merged the datetime should be unique. Here is to illustrate the resulting 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

I have spent a while trying to come up with solutions. In my mind I have the solution that is to do 2 separate selects, one for each datacenter, combine them with UNION and then just group them by datetime, but I'm sure this is terrible and it is not even running, there is an error about invalid syntax near GROUP. Here is the attempt: 

(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

Thanks, any help is appreciated!

pivot date tsql sql-server sql
2 Answers

I would recommend conditional aggregation.

Unlike vendor-specific pivot syntax, this is a portable approach (ie it works on most databases), which usually performs at least as good, or better, as vendor-specific implementation.

It is also more flexible - this does not make a difference for your specific use case, but would do in more complex cases, that pivot can't accomodate (like: list machines from DC01 whose id is 510 or greater, and machines from DC02 whose id is 120 or less).

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
user_106767160 
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

user_95177690
Related