test.RUN.sql

IF OBJECT_ID (N'dbo.RUN') is not null drop table RUN

CREATE TABLE RUN (
 ID int IDENTITY,
 NAME varchar(20),
 STAGE tinyint,
 TIME time)
;
insert into RUN (NAME,STAGE,TIME)
VALUES
('run1', 1,'00:10:44'),
('run1', 2,'00:08:00'),
('run1', 3,'00:09:00'),
('run2', 1,'00:11:00'),
('run2', 2,'00:09:44'),
('run2', 3,'00:08:33'),
('run3', 1,'00:07:00'),
('run3', 2,'00:10:30'),
('run3', 3,'00:09:15'),
('run4', 1,'00:10:15'),
('run4', 2,'00:11:33'),
('run4', 3,'00:08:15'),
('run5', 1,'00:07:15'),
('run5', 2,'00:09:33'),
('run5', 3,'00:10:00')
;



SELECT *
 ,LAG (TIME,1,'00:00') OVER (PARTITION BY STAGE ORDER BY TIME,ID) AS PREV_TIME_ST
 ,LAG (TIME,1,'00:00') OVER (PARTITION BY NAME ORDER BY TIME) AS PREV_TIME_NM
 ,FIRST_VALUE ([TIME]) OVER (PARTITION BY STAGE ORDER BY TIME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS PREV_TIME_FV
 ,FIRST_VALUE ([TIME]) OVER (PARTITION BY STAGE ORDER BY TIME) AS BEST_TIME
 ,ROW_NUMBER() OVER (PARTITION BY STAGE ORDER BY NAME) AS RN
 ,RANK() OVER (ORDER BY NAME) AS RANK
 ,DENSE_RANK () OVER (ORDER BY NAME) AS DR
 ,NTILE(3) OVER (ORDER BY NAME) AS NTILE
FROM RUN
ORDER BY STAGE, NAME


--PIVOT по этапам

WITH CTE AS (
			select NAME, STAGE, TIME
			from RUN
			)
SELECT NAME, [1],[2],[3]
FROM CTE
PIVOT (MIN(TIME)
FOR STAGE IN ([1],[2],[3])) AS P
ORDER BY NAME

--вариант PIVOT по бегунам

WITH CTE AS (
			select NAME, STAGE, TIME
			from RUN
			)
SELECT STAGE, run1,run2,run3,run4,run5
FROM CTE
PIVOT (MIN(TIME)
FOR NAME IN (run1,run2,run3,run4,run5)) AS P