CODEWITHSUNDEEP
sqloracle-database
John Smith
John Smith

Reputation: 69

SQL QUERY TO TRAVERSE DATA

I have a table as mentioned below

COLUMN_NAME OLD_VALUE   NEW_VALUE
:-----      :-------    :------------
FRST_NM     MICHAEL     TEST
LST_NM      DAVIS       TEST_IQVIA
MID_NM      AUSTIN      TEST

I have used the SQL as mentioned below

       select 
     (  case when COLUMN_NAME = 'FRST_NM' then NVL2 (new_value , new_value  ,  old_value )  else NULL  end )  FRST_NM,
      (  case when COLUMN_NAME = 'LST_NM' then NVL2 (new_value , new_value  ,  old_value )  else NULL end )  LST_NM,
         (  case when COLUMN_NAME = 'MID_NM' then NVL2 (new_value , new_value  ,  old_value )  else NULL end )  MID_NM
         from TEST_TABLE

Getting output like the mentioned below .

FRST_NM  LST_NM       MID_NM 
:------- :--------    :----------
TEST     NULL         NULL
NULL     TEST_IQVIA   NULL
NULL     NULL         TEST

The expected output would be

FRST_NM  LST_NM       MID_NM 
:------  :-------     :-------
TEST     TEST_IQVIA   TEST

Could anyone please guide me

Upvotes: 1

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use aggregation:

select max(case when COLUMN_NAME = 'FRST_NM' then coalesce(new_value, old_value) end) as FRST_NM,
       max(case when COLUMN_NAME = 'LST_NM' then coalesce(new_value, old_value ) end)  as LST_NM,
       max(case when COLUMN_NAME = 'MID_NM' then coalesce(new_value, old_value) end) as  MID_NM
from TEST_TABLE;

Notes:

  • nvl2() is not appropriate here. Although you could use nvl(), you might as well use the SQL standard function coalesce().
  • else null is redundant -- NULL is the default value.

Upvotes: 2

Mahamoutou
Mahamoutou

Reputation: 1555

You need to pivot columns like below :

select *
from your_table
pivot(
max(nvl(NEW_VALUE, OLD_VALUE)) for COLUMN_NAME in (
    'FRST_NM' as FRST_NM
  , 'LST_NM' as LST_NM
  , 'MID_NM' as MID_NM
  )
)
;

Upvotes: 0

Related Questions