0

I need to split single record into multiple record with columns that are not null.

Below is the table

enter image description here

Expected result

enter image description here

Scenario :

  1. col1 to col4 acts as primary key i need col5 to col7 in each row.
  2. col5 to col7 null values and 0 values should be ignored

Here is the query:

create table TABLE_1 (
    col1 number,
    col2 number,
    col3 number,
    col4 number,
    col5 number,
    col6 number,
    col7 number
);

insert into TABLE_1  values (100,101,102,103,1000,NULL,1002);
insert into TABLE_1  values (200,201,202,203,2000,2001,NULL);
insert into TABLE_1  values (300,301,302,303,NULL,3001,3002);
insert into TABLE_1  values (400,401,402,403,4000,NULL,4002);
MT0
  • 143,790
  • 11
  • 59
  • 117
Erik
  • 307
  • 5
  • 15
  • `UNPIVOT` like this [fiddle](https://dbfiddle.uk/ufJ9hQOi) – MT0 Aug 21 '23 at 09:45
  • Even changing the question slightly, its still a duplicate (all you need to do is add a `WHERE newcol != 0` filter to the end of the query in the fiddle in the previous comment to solve the additional constraint of ignoring values that are `0` as `null` values are ignored by default with `UNPIVOT`). – MT0 Aug 21 '23 at 10:27
  • Please read [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/q/285551/354577) Instead, format code as a [code block]. The easiest way to do this is to paste the code as text directly into your question, then select it and click the code block button. – ChrisGPT was on strike Aug 21 '23 at 11:36

0 Answers0