Oracle - 将行转换为列 [英] Oracle - Transpose rows into columns

问题描述

这是我一直在处理的表格中的一个示例:

创建表TEST_MC(CLUSTER_K 整数，PROCESS_PHASE 整数，SEM_AGG 字符(1));CLUSTER_K PROCESS_PHASE SEM_AGG==================================328 1 M----------------------------------328 2 A----------------------------------328 3 A

现在，我想转置和折叠行以获得以下结果:

 CLUSTER_K SEM_AGG_1 SEM_AGG_2 SEM_AGG_3==============================================第328话

我如何实现这一点(枢轴和/或分析功能)?

非常感谢任何帮助.

解决方案

如果不需要动态列名，可以使用 Pivot

with tab as(选择 328 作为 CLUSTER_K, 1 作为 process_phase, 'M' 作为 sem_agg from dual union all选择 328 作为 CLUSTER_K, 2 作为 process_phase, 'A' 作为 sem_agg from dual union all选择 328 作为 CLUSTER_K，选择 3 作为 process_phase，从 dual 中选择 'A' 作为 sem_agg)选择  *从选项卡枢轴 (max(sem_agg) for process_phase in (1 as sem_agg_1, 2 as sem_agg_2, 3 as sem_agg_3))

db<>fiddle 这里

Here is a sample from the table I have been working on:

CREATE TABLE TEST_MC
(
 CLUSTER_K INTEGER,
 PROCESS_PHASE INTEGER,
 SEM_AGG CHAR(1)
);

CLUSTER_K  PROCESS_PHASE   SEM_AGG
==================================
    328          1            M
----------------------------------
    328          2            A
----------------------------------
    328          3            A

Now, I would like to transpose and collapse rows obtaining the following result:

 CLUSTER_K  SEM_AGG_1   SEM_AGG_2   SEM_AGG_3
=============================================
    328         M           A           A

How i can achieve this (Pivot and/or analytical functions)?

Any help is greatly appreciated.

解决方案

if you don't Need dynamic column names you can use Pivot

with tab as(
  select 328 as CLUSTER_K, 1 as process_phase, 'M' as sem_agg from dual union all
  select 328 as CLUSTER_K, 2 as process_phase, 'A' as sem_agg from dual union all
  select 328 as CLUSTER_K, 3 as process_phase, 'A' as sem_agg from dual
)
select  *
from tab 
pivot (max(sem_agg) for process_phase in (1 as sem_agg_1, 2 as sem_agg_2, 3 as sem_agg_3))

db<>fiddle here

这篇关于Oracle - 将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！