如何用SQL实现动态行列转换生成TAT报表
2026-7-24
制作TAT报表的数据转换需求
数据背景
我们有一张存储案例TAT数据的表（Table A），字段包括：案例编号（Case）、用户名（User）、队列名（Queue）、用户在指定队列中打开案例的时长（TAT，单位：分钟）。
数据特征：
- 单个案例可能流转过多个队列，例如案例1经过queue1、queue2，案例2经过queue1、queue3；
- 同一用户可在同一队列多次打开同一案例，不同用户也可在同一队列多次打开同一案例。
已汇总的原始数据
| Case | Queue | User | TAT |
|---|---|---|---|
| COB-0000000001-process | Branch_Maker | User A | 90 |
| COB-0000000001-process | Branch_Maker | User B | 122 |
| COB-0000000001-process | Branch_Maker | User A | 25 |
| COB-0000000001-process | Branch_Maker | User C | 27 |
| COB-0000000001-process | Branch_Maker | User D | 30 |
| COB-0000000001-process | Branch_Maker | User A | 40 |
| COB-0000000001-process | Branch_Checker | User A | 120 |
| COB-0000000001-process | Branch_Checker | User E | 100 |
| COB-0000000004-process | Compliance | User F | 10 |
| COB-0000000004-process | Compliance | User A | 15 |
| COB-0000000004-process | Compliance | User B | 20 |
| COB-0000000004-process | Branch_Maker | User A | 20 |
| COB-0000000004-process | Branch_Checker | User H | 10 |
| COB-0000000004-process | Branch_Checker | User A | 15 |
| COB-0000000004-process | Director | User M | 20 |
| COB-0000000004-process | Director | User N | 23 |
| COB-0000000004-process | LPD | User O | 20 |
| COB-0000000004-process | RCD | User J | 30 |
目标报表格式
| Case | Branch_Maker User | Branch_Maker TAT | Branch_Checker User | Branch_Checker TAT | Compliance User | Compliance TAT | Director User | Director TAT | LPD User | LPD TAT | RCD User | RCD TAT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| COB-0000000001-process | User A | 155 | User A | 120 | ||||||||
| COB-0000000001-process | User B | 122 | ||||||||||
| COB-0000000001-process | User C | 27 | ||||||||||
| COB-0000000001-process | User D | 30 | ||||||||||
| COB-0000000001-process | User E | 100 | ||||||||||
| COB-0000000004-process | User A | 20 | User A | 15 | ||||||||
| COB-0000000004-process | User F | 10 | ||||||||||
| COB-0000000004-process | User A | 15 | ||||||||||
| COB-0000000004-process | User B | 20 | ||||||||||
| COB-0000000004-process | User H | 10 | ||||||||||
| COB-0000000004-process | User M | 20 | ||||||||||
| COB-0000000004-process | User N | 30 | ||||||||||
| COB-0000000004-process | User O | 20 | ||||||||||
| COB-0000000004-process | User J | 30 |
内容的提问来源于stack exchange，提问作者MD KAMRAN AZAM
相关产品推荐
相关产品推荐
