SQL Server : converting times table horizontally

I have SQL Server and a simple table that will be populated automatically from time attendance machines:

[time_id],[time_resource_id],[time_type],[time_date],[time_hour],[time_establishment]
     1          123               in     2021-06-19     8:00          1
     2          123               out    2021-06-19     10:00
     3          123               in     2021-06-19     13:00

I would like to convert this to something like

time_resource_id,   time_date         t1 ,       t2     ,     t3
-------------------------------------------------------------------
123                2021-06-19         8:00      10:00       13:00

up to a max of t8.

How can I achieve this? I think something like pivot.

Thanks

1 answer

  • answered 2021-06-19 18:02 Gordon Linoff

    You can use row_number() and conditional aggregation:

    select time_resource_id, time_date,
           max(case when seqnum = 1 then time_hour end) as t1,
           max(case when seqnum = 2 then time_hour end) as t2,
           max(case when seqnum = 3 then time_hour end) as t3,
           max(case when seqnum = 4 then time_hour end) as t4,
           max(case when seqnum = 5 then time_hour end) as t5,
           max(case when seqnum = 6 then time_hour end) as t6,
           max(case when seqnum = 7 then time_hour end) as t7,
           max(case when seqnum = 8 then time_hour end) as t8
    from (select t.*,
                 row_number() over (partition by time_resource_id, time_date order by time_id) as seqnum
          from t
         ) t
    group by time_resource_id, time_date