如何为ID从0开始计数并将宽表转换为指定长表格式？

我编写了如下SQL查询语句，执行后得到对应的宽表输出：

with tb1 as
(
    select  id
        ,row_number() over(order by id)-1 as lag_0
    from id_store
),
tb2 as
(
    select *
        ,lag(lag_0) over(order by id) as lag_1
    from tb1 
),
tb3 as
(
    select *
        ,lag(lag_1) over(order by id) as lag_2
    from tb2
),
tb4 as
(
    select *
        ,lag(lag_2) over(order by id) as lag_3
    from tb3
)
    select *
         ,lag(lag_3) over(order by id) as lag_4
    from tb4

执行后得到的宽表结果：

我希望得到如下指定格式的长表输出（为简化仅使用5个id），此前尝试交叉连接但结果异常，请问该如何实现？

目标长表：

解决方案 #

通用方案（适用于所有SQL方言）

使用UNION ALL将宽表的每一列拆分为独立行，这是最通用的方式，不受SQL方言限制：

with tb1 as
(
    select  id
        ,row_number() over(order by id)-1 as lag_0
    from id_store
),
tb2 as
(
    select *
        ,lag(lag_0) over(order by id) as lag_1
    from tb1 
),
tb3 as
(
    select *
        ,lag(lag_1) over(order by id) as lag_2
    from tb2
),
tb4 as
(
    select *
        ,lag(lag_2) over(order by id) as lag_3
    from tb3
),
wide_table as (
    select *
         ,lag(lag_3) over(order by id) as lag_4
    from tb4
)
-- 拆分每一列
select id, 'lag_0' as group_name, lag_0 as ord_num from wide_table
union all
select id, 'lag_1' as group_name, lag_1 as ord_num from wide_table
union all
select id, 'lag_2' as group_name, lag_2 as ord_num from wide_table
union all
select id, 'lag_3' as group_name, lag_3 as ord_num from wide_table
union all
select id, 'lag_4' as group_name, lag_4 as ord_num from wide_table
-- 按分组和id排序，匹配目标格式
order by group_name, id;

专用方案（支持UNPIVOT的SQL方言，如SQL Server、Oracle）

如果你的数据库支持UNPIVOT语法，可以用更简洁的写法：

-- 以SQL Server为例
with tb1 as
(
    select  id
        ,row_number() over(order by id)-1 as lag_0
    from id_store
),
tb2 as
(
    select *
        ,lag(lag_0) over(order by id) as lag_1
    from tb1 
),
tb3 as
(
    select *
        ,lag(lag_1) over(order by id) as lag_2
    from tb2
),
tb4 as
(
    select *
        ,lag(lag_2) over(order by id) as lag_3
    from tb3
),
wide_table as (
    select *
         ,lag(lag_3) over(order by id) as lag_4
    from tb4
)
select id, group_name, ord_num
from wide_table
-- 直接将指定列转换为行
unpivot (
    ord_num for group_name in (lag_0, lag_1, lag_2, lag_3, lag_4)
) as unpvt
order by group_name, id;

两种方案都能将宽表转换为你需要的长表格式，UNION ALL兼容性更强，UNPIVOT写法更简洁，可根据你的数据库类型选择。

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