将表格拆分为不同级别的列 [英] Split table into different level columns

问题描述

我需要根据级别将数据分为3个不同的列.下表SU_DC_1是基于SETTLEUNIT列的最高级别，我需要添加该级别.

I have got a requirement to split the data based on the level into 3 different columns. In the below table SU_DC_1 is the top level based on SETTLEUNIT column I need to add the level.

父母:TOPSETTLUNIT 孩子:SETTLEUNIT

Parent : TOPSETTLUNIT Child : SETTLEUNIT

基于孩子，我需要再创建3列

Based on Child I need to create 3 additional columns

在上面的层次结构中被提到为

In the above hierarchy is mentioned as

• 输出:

我发现很难为其编写查询.你能帮忙吗?

I am finding it difficult to write the query for it. Could you please help .

推荐答案

类似的东西

SELECT topsettlunit,
       settleunit,
       CONNECT_BY_ROOT( topsettlunit ) AS level1,
       CASE LEVEL
         WHEN 1 THEN settleunit
         WHEN 2 THEN topsettlunit
       END AS level2,
       CASE LEVEL
         WHEN 2 THEN settleunit
       END AS level3
FROM   your_table
START WITH topsettlunit NOT IN ( SELECT settleunit FROM your_table )
CONNECT BY PRIOR settleunit = topsettlunit;

这篇关于将表格拆分为不同级别的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！