Search code examples
sqloracle-databaseoracle19c

How to get data with values in columns matching to primary key in same row?

I am having below table

table 1

and i need data like "account_number with any column which doesn't have null "

conditions:

  1. Account number column will not have "null". it always have values
  2. Null columns should be ignored.
  3. In each row ( in table ) account number and not null columns should form a one record .
  4. Code should be in SQl not in PL/SQL.

below is the sample output format

enter image description here

Table syntax:

CREATE TABLE demo (acc_no number primary key, id_no varchar2(20), id_no2 varchar2(20), id_no3 number, lice_no number)

INSERT INTO demo (acc_no,id_no,id_no2) VALUES ( '452', '452', '452');
INSERT INTO demo (acc_no,id_no3,lice_no) VALUES ('411','568','987');
Solution

  • This is Oracle, and there's no text datatype so I switched to varchar2.

    SQL> desc demo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACC_NO                                    NOT NULL NUMBER(38)
 ID_NO                                              VARCHAR2(20)
 ID_NO2                                             VARCHAR2(20)
 ID_NO3                                             NUMBER(38)
 LICE_NO                                            NUMBER(38)

SQL> select * From demo;

    ACC_NO ID_NO                ID_NO2                   ID_NO3    LICE_NO
---------- -------------------- -------------------- ---------- ----------
       452 452                  452
       411                                                  568        987

    A trivial option is to union columns to get rows; as datatypes must match, I used to_char on integer columns:

    SQL> select acc_no, id_no            from demo where id_no   is not null
  2  union all
  3  select acc_no, id_no2           from demo where id_no2  is not null
  4  union all
  5  select acc_no, to_char(id_no3)  from demo where id_no3  is not null
  6  union all
  7  select acc_no, to_char(lice_no) from demo where lice_no is not null;

    ACC_NO ID_NO
---------- ----------------------------------------
       452 452
       452 452
       411 568
       411 987

SQL>