How to turn a column of timestamps into two columns depending on separate column without splitting rows
Solution 1:
I'd start with self-join.
SELECT
chcecked.STATUS_DATETIME as CHECKED_IN_TIME,
completed.STATUS_DATETIME as COMPLETED_TIME
FROM
yourtable as checked
JOIN
yourtable as completed
ON ....
Solution 2:
This is just an example of how to use pivot
which is an addition to Simeon's answer.Using sample data from the image provided.
Table creation and data insertion:
create or replace temporary table _temp (
ts timestamp_ntz,
_status varchar
);
insert into _temp
values ('2021-12-11 11:12:03','created'),
('2021-12-11 11:12:03','checked_in'),
('2021-12-11 11:22:49','progress'),
('2021-12-11 11:55:03','completed');
Pivot query:
select *
from _temp
pivot(max(ts) for _status in ('checked_in', 'completed')) as p;
Result:
'checked_in' 'completed'
2021-12-11 11:12:03.000 2021-12-11 11:55:03.000
Note that I've used MAX
aggregate function which can be replaced by other aggregate functions. This would always return a single row if there are only 2 columns, to get a better sense of pivot have another column and take a look at examples provided in Pivot's doc.