Is there anyway to get only the specific row and column in your table that is null?
Sample TABLE
PK COL1 COL2 COL3
1 AA BB CC
2 BB CC
3 AA
Assume Result would be:
PK AK NewCOL
1 2 COL1
2 3 COL2
3 3 COL3
*AK is the PK of the first table.
I researched about this :
https://dba.stackexchange.com/questions/28726/select-column-names-whose-entries-are-not-null
but sadly, it does not work.
select PK, 'COL1' from sampleTable where COL1 IS NULL
UNION
select PK, 'COL2' from sampleTable where COL2 IS NULL
UNION
select PK, 'COL3' from sampleTable where COL3 IS NULL
You can wrap this around rownum to get your first column.
Below query will returns the column names which will have null values along with pk values. this information you can copy to other table.
select pk,nvl(col1,'col1') as columns_1 from table
where col1 is null
union all
select pk,nvl(col2,'col2') as columns_1 from table
where col2 is null
union all
select pk,nvl(col3,'col3') as columns_1 from table
where col3 is null
Here's an alternative to the UNION ALL methods, which may (or may not!) be quicker than reading from the same table multiple times:
with sample_data as (select 1 pk, 'AA' col1, 'BB' col2, 'CC' col3 from dual union all
select 2 pk, null col1, 'BB' col2, 'CC' col3 from dual union all
select 3 pk, 'AA' col1, null col2, null col3 from dual)
-- end of mimicking a table called sample_data with data in it
select row_number() over (order by sd.pk, d.id) pk, -- only for demo or reporting purposes; use a sequence.nextval for production code, if you're inserting this data somewhere.
sd.pk ak,
case when d.id = 1 then 'COL1'
when d.id = 2 then 'COL2'
when d.id = 3 then 'COL3'
end newcol
from sample_data sd
inner join (select level id
from dual
connect by level <= 3) d on ( (sd.col1 is null and d.id = 1)
or (sd.col2 is null and d.id = 2)
or (sd.col3 is null and d.id = 3));
PK AK NEWCOL
---------- ---------- ------
1 2 COL1
2 3 COL2
3 3 COL3
