Transform table from rows to columns in oracle SQL-CodePudding

Transform table from rows to columns

Existing table A

How do i transform from the first table to the second table below?

Expected results

If i do something like the following sql statement, i only get them in separate rows instead of the related ones in a single row

SELECT 
        CASE WHEN LENGTH(CODE) = 2 THEN NAME
        ELSE NULL
        END AS CODE1,
        CASE WHEN LENGTH(CODE) = 4 THEN NAME
        ELSE NULL
        END AS CODE2,
        CASE WHEN LENGTH(CODE) = 6 THEN NAME
        ELSE NULL
        END AS CODE3,
        CASE WHEN LENGTH(CODE) = 8 THEN NAME
        ELSE NULL
        END AS CODE4
FROM TABLEA;

CodePudding user response：

You need to select the max of the various CASE expressions:

SELECT 
    MAX(CASE WHEN LENGTH(CODE) = 2 THEN NAME END) AS CODE1,
    MAX(CASE WHEN LENGTH(CODE) = 4 THEN NAME END) AS CODE2,
    MAX(CASE WHEN LENGTH(CODE) = 6 THEN NAME END) AS CODE3,
    MAX(CASE WHEN LENGTH(CODE) = 8 THEN NAME END) AS CODE4
FROM TABLEA;

CodePudding user response：

Here's one way to do it:

create table table_a (
    code varchar(8),
    name varchar(50));
    

insert into table_a 
values ('25','ABC-25'), ('2510','BDC-2510'), ('251010','EFG-251010'), ('25101010','PIT-25101010');


select * from  table_a;


with 
code1 as (select code, name from table_a where length(code)=2),
code2 as (select code, name from table_a where length(code)=4),
code3 as (select code, name from table_a where length(code)=6),
code4 as (select code, name from table_a where length(code)=8)
select c1.code as code1,
       c1.name as code_name1,
       c2.code as code2,
       c2.name as code_name2,
       c3.code as code3,
       c3.name as code_name3,
       c4.code as code4,
       c4.name as code_name4
  from code4 c4
  join code3 c3
    on substr(c4.code,1,6) = c3.code
  join code2 c2
    on substr(c3.code,1,4) = c2.code
  join code1 c1
    on substr(c2.code,1,2) = c1.code;

Result:

code1|code_name1|code2|code_name2|code3 |code_name3|code4   |code_name4  |
----- ---------- ----- ---------- ------ ---------- -------- ------------ 
25   |ABC-25    |2510 |BDC-2510  |251010|EFG-251010|25101010|PIT-25101010|