pivot, Sql Server - rextester

Language: Layout:

create table machines(MachineId varchar(50), Alias varchar(30));
insert into machines values
('92649C7B-E962-4EB1-B631-00086EECA98A','Alias 1'),
('A2649C7B-E962-4EB1-B631-00086EECA98A','Alias 2');

create table states(MachineId varchar(50), State varchar(50), Minutes int);
insert into states values
('92649C7B-E962-4EB1-B631-00086EECA98A','State1', 50),
('92649C7B-E962-4EB1-B631-00086EECA98A','State2', 50),
('92649C7B-E962-4EB1-B631-00086EECA98A','State500', 50),
('92649C7B-E962-4EB1-B631-00086EECA98A','State1', 50),
('92649C7B-E962-4EB1-B631-00086EECA98A','State2', 50),
('92649C7B-E962-4EB1-B631-00086EECA98A','State500', 50),
('A2649C7B-E962-4EB1-B631-00086EECA98A','State1', 10),
('A2649C7B-E962-4EB1-B631-00086EECA98A','State2', 20),
('A2649C7B-E962-4EB1-B631-00086EECA98A','State500', 70);

WITH Calc AS
(
    SELECT m.MachineId,
           m.Alias,
           Minutes,
           s.State
    FROM machines m
         INNER JOIN states s ON m.MachineId = s.MachineId
)
SELECT MachineId, Alias, [State1], [State2], [State500]
FROM
(SELECT MachineId, Alias, State, Minutes FROM Calc) AS SourceTable
PIVOT
(
    SUM(Minutes) FOR State IN ([State1],[State2],[State500])
) AS PivotTable;

drop table machines;
drop table states;

View schema
Execution time: 0,03 sec, rows selected: 2, rows affected: 11, absolute service time: 0,17 sec