Finding latest timestamp per value across columns and rows

I have a database table with rows like

Id | s1 | ts1 | s2 | ts2 | ... | s6 | ts6

​x
 
Id | s1 | ts1 | s2 | ts2 | ... | s6 | ts6​

Where ID is the non-unique identifier for the rows and will be used to filter the initial dataset. Fields s1 to s6 contain values and columns ts1 to ts6 contain their timestamps. And I need to find the latest (or first) timestamp per “s” value. The “s” values are not unique and the same values might be in any of the “s” columns.

I’ve not tried anything yet, because I have no idea how to turn those columns into rows. If I knew, then I’d probably find the latest (with max function) per value. But how to turn them into rows? I understand there is PIVOT function, but I’m not entirely sure how to use it and how to turn those columns into single “s” and “ts” columns.

Edit:

Sample dataset:

ID     s1 ts1        s2 ts2        s3 ts3        s4 ts4        s5 ts5        s6 ts6
123456 aa 1647456495 ab 1647456495 ac 1647456495 ad 1647456495 ae 1647456495 af 1647456495
123456 ax 1647456495 aa 1647456495 af 1647456495 al 1647456495 ai 1647456495 as 1647456495
123456 ab 1647456495 aa 1647456495 ad 1647456495 ac 1647456495 ae 1647456495 af 1647456495

 
ID     s1 ts1        s2 ts2        s3 ts3        s4 ts4        s5 ts5        s6 ts6123456 aa 1647456495 ab 1647456495 ac 1647456495 ad 1647456495 ae 1647456495 af 1647456495123456 ax 1647456495 aa 1647456495 af 1647456495 al 1647456495 ai 1647456495 as 1647456495123456 ab 1647456495 aa 1647456495 ad 1647456495 ac 1647456495 ae 1647456495 af 1647456495​

Sample output:

s_value ts_value
aa      1647456495
ab      1647456495
af      1647456495
...
ax      1647456495

 
s_value ts_valueaa      1647456495ab      1647456495af      1647456495...ax      1647456495​

I used the same unix epoch timestamp in the example, but this is much what the data looks like. It’s going to be about 20-100k rows per each dataset and 10-30 different values for “s” fields. Not sure if this is relevant or not.

Answer

Thank you @Conor Cunningham MSFT for the suggestion, this would be a good solution. I have reproed and got the expected results and posting it as the answer.

Source:

-- get the distinct values of 's' and 't' columns
select s1 as s_values, t1 as t_values  into #temp_latest from test_tb 
union  
select s2, t2 from test_tb
union  
select s3, t3 from test_tb
union 
select s4, t4 from test_tb
union 
select s5, t5 from test_tb
union 
select s6, t6 from test_tb

-- to get the max t values for same s value
select s_values, max(t_values) t_values from #temp_latest 
group by s_values

 
-- get the distinct values of 's' and 't' columnsselect s1 as s_values, t1 as t_values  into #temp_latest from test_tb union  select s2, t2 from test_tbunion  select s3, t3 from test_tbunion select s4, t4 from test_tbunion select s5, t5 from test_tbunion select s6, t6 from test_tb​-- to get the max t values for same s valueselect s_values, max(t_values) t_values from #temp_latest group by s_values​

Advertisement

Answer