I have a database table with rows like
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
Sample output:
s_value ts_value aa 1647456495 ab 1647456495 af 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.
Advertisement
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
