Forum Stats

  • 3,733,722 Users
  • 2,246,811 Discussions
  • 7,856,856 Comments

Discussions

I have requirement that entire column values are null want to adjust as per expected format

User_TIFB4
User_TIFB4 Member Posts: 3 Red Ribbon

I have Source Data that entire column values are null , so want to adjust the column values in replace of null column as per Expected Data format. Please help on this.

CREATE TABLE T_SAMP (COLUMN1 VARCHAR2(20), COLUMN2 VARCHAR2(20),COLUMN3 VARCHAR2(20),

COLUMN4 VARCHAR2(20),COLUMN5 VARCHAR2(20),COLUMN6 VARCHAR2(20),COLUMN7 VARCHAR2(20) );


INSERT INTO T_SAMP VALUES('ROW1',6620,NULL,6623,NULL,NULL,6623);

INSERT INTO T_SAMP VALUES('ROW2',6610,NULL,6622,NULL,NULL,5534);

INSERT INTO T_SAMP VALUES('ROW3',6620,NULL,6616,NULL,NULL,3342);

INSERT INTO T_SAMP VALUES('ROW4',6623,NULL,3655,NULL,NULL,3444);

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,315 Blue Diamond
    Accepted Answer
    with cols as (
      select listagg(col_name, ', ') within group (order by col_name) as col_list
      from (
       select col_name, max(col_val) max_col_val
       from t_samp
       unpivot include nulls (
        col_val
        for col_name in (
         column1, column2, column3, column4, column5, column6, column7
        )
       )
       group by col_name
      )
      where max_col_val is not null
    )
    select replace('insert into t_samp (<%col_list%>) select <%col_list%> from t_samp', '<%col_list%>', col_list) as SQL
    from cols
    
    SQL
    --------------------
    insert into t_samp (COLUMN1, COLUMN2, COLUMN4, COLUMN7) select COLUMN1, COLUMN2, COLUMN4, COLUMN7 from t_samp
    


    User_TIFB4

Answers

  • Paulzip
    Paulzip Member Posts: 8,315 Blue Diamond

    You cannot normally** dynamically remove columns in SQL. The SQL engine needs to know the column projections at parse time. You could dynamically generate the SQL required and then execute that :

    select 'select '||coalesce(listagg(col_name, ', ') within group (order by col_name), 'null as X')||' from T_SAMP order by 1' SQL
    from (
     select col_name, max(col_val) max_col_val
     from t_samp
     unpivot include nulls (
      col_val
      for col_name in (
       column1, column2, column3, column4, column5, column6, column7
      )
     )
     group by col_name
    )
    where max_col_val is not null;
    
    SQL
    -------------------
    select COLUMN1, COLUMN2, COLUMN4, COLUMN7 from T_SAMP order by 1
    
    

    ** If you have 18c or above, you could use Polymorphic Table Functions or 19c you could use SQL Macros and in both cases it is possible, however, you would still need to run something like the code above to know which columns to remove.

    User_TIFB4
  • User_TIFB4
    User_TIFB4 Member Posts: 3 Red Ribbon

    Hi ,

    The solution given by you really helpful and 90% achieved the result as expected. Also Kindly help me to reframe the given solution query by you based on Select column list ( in this case COLUMN1,COLUMN2,COLUMN4,COLUMN7) = 4 .. Then the Insert statement will be like want column list dynamically ..

    Insert into T_SAMP (COLUMN1,COLUMN2,COLUMN3,COLUMN4) SELECT COLUMN1,COLUMN2,COLUMN4,COLUMN7 FROM T_SAMP order by 1

    If there are 6 columns return by your query then I want insert statement like Insert into T_SAMP (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6) etc.. Thanks for your help in advance.

  • Paulzip
    Paulzip Member Posts: 8,315 Blue Diamond
    Accepted Answer
    with cols as (
      select listagg(col_name, ', ') within group (order by col_name) as col_list
      from (
       select col_name, max(col_val) max_col_val
       from t_samp
       unpivot include nulls (
        col_val
        for col_name in (
         column1, column2, column3, column4, column5, column6, column7
        )
       )
       group by col_name
      )
      where max_col_val is not null
    )
    select replace('insert into t_samp (<%col_list%>) select <%col_list%> from t_samp', '<%col_list%>', col_list) as SQL
    from cols
    
    SQL
    --------------------
    insert into t_samp (COLUMN1, COLUMN2, COLUMN4, COLUMN7) select COLUMN1, COLUMN2, COLUMN4, COLUMN7 from t_samp
    


    User_TIFB4
  • User_TIFB4
    User_TIFB4 Member Posts: 3 Red Ribbon

    Thanks for providing the solution. This works for me

Sign In or Register to comment.