时间戳

首页 > 解决方案 > 如何将oracle中的行转换为特定的列集？

sql - 如何将oracle中的行转换为特定的列集？

问题描述

我有一个表，其中有 2 列。我想将行转换为 4 列的集合。例如，我的表：

第 1 列 第 2 列
1 ñ
2 ñ
3 ñ
4 ñ
5 ñ

我想将它们转换为：

第 1 列 Columnn2 第 3 列 第 4 列
1 2 3 4
5 ñ ñ ñ
ñ ñ

我怎样才能在 Oracle 中做到这一点？

谢谢您的帮助！

标签： sqloracletranspose

解决方案

这是一种选择；阅读代码中的注释。

SQL> with
  2  test (col1, col2) as
  3    -- sample data
  4    (select 1, 'N' from dual union all
  5     select 2, 'N' from dual union all
  6     select 3, 'N' from dual union all
  7     select 4, 'N' from dual union all
  8     select 5, 'N' from dual
  9    ),
 10  temp as
 11    -- union of two columns will produce a single-column "table"
 12    (select to_char(col1) col from test union all
 13     select col2              from test
 14    ),
 15  temp2 as
 16    -- ordinal numbers for each row
 17    (select col,
 18            row_number() over (order by null) rn
 19     from temp
 20    ),
 21  temp3 as
 22    -- a little bit of calculation so that you'd be able to create groups of 4 columns
 23    (select col,
 24            mod(rn, 4) rn,
 25            ceil(rn / 4) grp
 26     from temp2
 27    )
 28  -- final query
 29  select max(case when rn = 1 then col end) col1,
 30         max(case when rn = 2 then col end) col2,
 31         max(case when rn = 3 then col end) col3,
 32         max(case when rn = 0 then col end) col4
 33  from temp3
 34  group by grp;

COL1  COL2  COL3  COL4
----- ----- ----- -----
1     2     3     4
5     N     N     N
N     N

SQL>

推荐阅读