sql - SQL Server:水平转换时间表
问题描述
我有 SQL Server 和一个简单的表,它将从考勤机自动填充:
[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
我想把它转换成类似的东西
time_resource_id, time_date t1 , t2 , t3
-------------------------------------------------------------------
123 2021-06-19 8:00 10:00 13:00
最高可达 t8。
我怎样才能做到这一点?我认为像枢轴一样的东西。
谢谢
解决方案
您可以使用row_number()
条件聚合:
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
推荐阅读
- python - opencensus stats python stackdriver exporter可以在python 2 app engine标准环境下使用吗?
- javascript - Apple Touch Bar 键未触发按键事件
- sql - 如何查询在 AWS Athena 中使用 Presto 的列上分区的表
- regex - 正则表达式删除字符结束 xml
- ios - Swift 数组索引超出范围和 Nil 值
- jquery - 将对象从 getJSON 传递到外部函数?
- c++ - PocketSphinx ps_process_raw 样本缓冲区大小影响
- google-cloud-platform - Dataproc 更改工作器大小
- c++ - Text file Issues (Visual C++)
- ruby-on-rails - 是否有用于 nodejs/expressjs 的 RailsPanel?