"pivot" table by datediff

Question

I have the following data in a SQL table

date	data_id	data_value
2022-05-02 11:17:10.9033333	camTray_x	4.989
2022-05-02 11:17:10.9033333	camTray_y	1.308
2022-05-02 11:17:14.0966667	camTray_x	1.469
2022-05-02 11:17:14.1066667	camTray_y	2.845

I want to achieve the following result based on the time difference between 2 records (difference can be 0 and less than 100ms)

date	X	Y
2022-05-02 11:17:10.9033333	4.989	1.308
2022-05-02 11:17:14.0966667	1.469	2.845

I am not a SQL expert and tried to mimic several simlar aproaches and I have a working solution like below

with xvalues as (select date, data_value as 'X' from _prod_data_line where data_id='camTray_x' ), 
yvalues as (select date, data_value as 'Y' from _prod_data_line where data_id='camTray_Y' )
    
select xvalues.date, xvalues.X, yvalues.Y from xvalues left join yvalues on 
      abs(datediff(millisecond, xvalues.date, yvalues.date))<100

Is this doable without the 2 selects ?

Answer 1

This will process the operation with a single scan instead of two, but it is more complex, so (as often is the case) you trade complexity for performance.

; -- see sqlblog.org/cte
WITH cte AS
(
  SELECT date, 
    X = data_value,
    Y = LEAD(CASE WHEN data_id = 'camTray_y' THEN data_value END,1) 
             OVER (ORDER BY date),
    delta = DATEDIFF(MILLISECOND, date, 
        LEAD(CASE WHEN data_id = 'camTray_y' THEN date END,1) 
             OVER (ORDER BY date))
  FROM dbo._prod_data_line
)
SELECT date, X, Y FROM cte WHERE delta <= 100;

Output:

date	X	Y
2022-05-02 11:17:10.9033333	4.989	1.308
2022-05-02 11:17:14.0966667	1.469	2.845

• Example db<>fiddle

Also, this is a simplification, because it assumes no x/y will overlap. If you want to handle those, please provide additional edge cases like that one and those mentioned in the comments, and explain how you want them handled.