SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table server_details    
(
  hostname varchar(20), category varchar(20), active bit
)
INSERT INTO server_details (hostname,category,active) VALUES 
('server1','small',1),             
('server2','big',1);              

create table server_stat
(
  hostname varchar(20), metric varchar(20), value int, lastupdated time
)
insert into server_stat (hostname,metric,value,lastupdated) VALUES 
('server1','cpu',10,'03:30:37'),
('server1','ram',25,'03:30:37'),
('server1','disk',57,'03:30:37'),
('server2','cpu',23,'03:30:37'),
('server2','ram',55,'03:30:37'),
('server2','disk',33,'03:30:37'),
('server1','cpu',13,'03:25:37'),
('server1','ram',25,'03:25:37'),
('server1','disk',43,'03:25:37'),
('server2','cpu',83,'03:25:37'),
('server2','ram',95,'03:25:37'),
('server2','disk',63,'03:25:37');

SELECT * FROM server_details;
SELECT * FROM server_stat;


hostname
category
active


server1
small
True

server2
big
True




hostname
metric
value
lastupdated


server1
cpu
10
03:30:37.0000000

server1
ram
25
03:30:37.0000000

server1
disk
57
03:30:37.0000000

server2
cpu
23
03:30:37.0000000

server2
ram
55
03:30:37.0000000

server2
disk
33
03:30:37.0000000

server1
cpu
13
03:25:37.0000000

server1
ram
25
03:25:37.0000000

server1
disk
43
03:25:37.0000000

server2
cpu
83
03:25:37.0000000

server2
ram
95
03:25:37.0000000

server2
disk
63
03:25:37.0000000


/*
result should look like with latest lastupdated value
|hostname|category|active|cpu|ram|disk|
---------------------------------------
|server1 |small   |yes   |10 |25 |57  |
|server2 |big     |yes   |23 |55 |33  |
*/
SELECT hd.hostname, hd.category, hd.active,
       (SELECT TOP 1 hs_cpu.value 
        FROM server_stat hs_cpu 
        WHERE hs_cpu.hostname = hd.hostname AND hs_cpu.metric = 'cpu' 
        ORDER BY hs_cpu.lastupdated DESC ) AS cpu,
       (SELECT TOP 1 hs_ram.value 
        FROM server_stat hs_ram 
        WHERE hs_ram.hostname = hd.hostname AND hs_ram.metric = 'ram' 
        ORDER BY hs_ram.lastupdated DESC) AS ram,
       (SELECT TOP 1 hs_disk.value 
        FROM server_stat hs_disk 
        WHERE hs_disk.hostname = hd.hostname AND hs_disk.metric = 'disk' 
        ORDER BY hs_disk.lastupdated DESC ) AS disk
FROM server_details hd;


hostname
category
active
cpu
ram
disk


server1
small
True
10
25
57

server2
big
True
23
55
33


    SELECT Latest.hostname, sd.category, sd.active, latest.metric, v.value
    FROM (
        SELECT hostname,metric, MAX(lastupdated) lastupdated
        FROM server_stat
        GROUP BY hostname,metric
    ) as Latest
    INNER JOIN server_stat v ON Latest.hostname = v.hostname AND Latest.metric = v.metric AND Latest.lastupdated = v.lastupdated
    INNER JOIN server_details sd on Latest.hostname = sd.hostname


hostname
category
active
metric
value


server2
big
True
ram
55

server2
big
True
disk
33

server2
big
True
cpu
23

server1
small
True
ram
25

server1
small
True
disk
57

server1
small
True
cpu
10


select *
from
(
    SELECT Latest.hostname, sd.category, sd.active, latest.metric, v.value
    FROM (
        SELECT hostname,metric, MAX(lastupdated) lastupdated
        FROM server_stat
        GROUP BY hostname,metric
    ) as Latest
    INNER JOIN server_stat v ON Latest.hostname = v.hostname AND Latest.metric = v.metric AND Latest.lastupdated = v.lastupdated
    INNER JOIN server_details sd on Latest.hostname = sd.hostname
) x
pivot
(
  max(value)
  for metric in([cpu],[ram],[disk])
) pvt


hostname
category
active
cpu
ram
disk


server1
small
True
10
25
57

server2
big
True
23
55
33

hostname	metric	value	lastupdated
server1	cpu	10	03:30:37.0000000
server1	ram	25	03:30:37.0000000
server1	disk	57	03:30:37.0000000
server2	cpu	23	03:30:37.0000000
server2	ram	55	03:30:37.0000000
server2	disk	33	03:30:37.0000000
server1	cpu	13	03:25:37.0000000
server1	ram	25	03:25:37.0000000
server1	disk	43	03:25:37.0000000
server2	cpu	83	03:25:37.0000000
server2	ram	95	03:25:37.0000000
server2	disk	63	03:25:37.0000000

hostname	category	active	metric	value
server2	big	True	ram	55
server2	big	True	disk	33
server2	big	True	cpu	23
server1	small	True	ram	25
server1	small	True	disk	57
server1	small	True	cpu	10