# "pivot" table by datediff

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 ?

### >Solution :

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

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.