时间戳

首页 > 解决方案 > 将单列转换为多行

sql - 将单列转换为多行

问题描述

我的输出如下：

03-19-2020
03-20-2020
03-21-2020
03-22-2020

如何将其更改为显示如下：

v_datecol1       v_datecol2    v_datecol3    ------ v_datecoln
03-19-2020       03-20-2020    03-21-2020          03-04-2020

我提到的 4 个日期作为示例，从今天开始我将有 25 个日期，我需要像上面一样在单独的列中

标签： sqloraclepivot

解决方案

尝试：

with dt_tab as
(
select '03-19-2020' dt from dual union
select '03-20-2020' dt from dual union
select '03-21-2020' dt from dual union
select '03-22-2020' dt from dual 
)
select listagg(dt,' ') within group (order by dt) rows_as_columns from dt_tab;

根据您的评论，您可以使用 PIVOT，例如：

with dt_tab as
(
select dt, rownum row_num from 
(
select '03-19-2020' dt from dual union
select '03-20-2020' dt from dual union
select '03-21-2020' dt from dual union
select '03-22-2020' dt from dual union
select '03-23-2020' dt from dual union
select '03-24-2020' dt from dual union
select '03-25-2020' dt from dual union
select '03-26-2020' dt from dual union
select '03-27-2020' dt from dual union
select '03-28-2020' dt from dual union
select '03-29-2020' dt from dual union
select '03-30-2020' dt from dual union
select '03-31-2020' dt from dual union
select '04-01-2020' dt from dual union
select '04-02-2020' dt from dual union
select '04-03-2020' dt from dual union
select '04-04-2020' dt from dual union
select '04-05-2020' dt from dual union
select '04-06-2020' dt from dual union
select '04-07-2020' dt from dual union
select '04-08-2020' dt from dual union
select '04-09-2020' dt from dual union
select '04-10-2020' dt from dual union
select '04-11-2020' dt from dual union
select '04-12-2020' dt from dual 
)
order by dt
)
select * from 
(
select dt_tab.* from dt_tab
)
pivot 
(
min(dt) for (row_num) in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)
)
;

推荐阅读