SQL实现单行列转多行：状态变更日期列转状态-时间戳行

输入数据 #

输出要求 #

将上述单行多阶段数据转换为多行单阶段数据，仅保留存在时间戳的阶段，输出格式如下：

实现SQL语句 #

SELECT story_id, 'Groomed' AS sched_state, story_being_groomed_ts AS last_up_ts
FROM your_table_name
WHERE story_being_groomed_ts IS NOT NULL
UNION ALL
SELECT story_id, 'Defined' AS sched_state, story_def_ts AS last_up_ts
FROM your_table_name
WHERE story_def_ts IS NOT NULL
UNION ALL
SELECT story_id, 'inprogress' AS sched_state, story_in_prgrs_ts AS last_up_ts
FROM your_table_name
WHERE story_in_prgrs_ts IS NOT NULL
UNION ALL
SELECT story_id, 'Completed' AS sched_state, story_cmpl_ts AS last_up_ts
FROM your_table_name
WHERE story_cmpl_ts IS NOT NULL
ORDER BY story_id, last_up_ts;

逻辑说明 #

• 用UNION ALL合并四个阶段的查询结果，每个子查询对应一个业务阶段，将原表的时间戳字段映射为last_up_ts，同时指定对应的阶段名称作为sched_state
• 每个子查询通过WHERE子句过滤掉时间戳为空的记录，确保只生成存在的阶段行
• 最后通过ORDER BY按story_id和时间戳排序，保证阶段顺序符合业务流转逻辑

内容的提问来源于stack exchange，提问作者user20998231