Is there any method to solve this problem using oracle SQL. Got stuck in this problem-CodePudding

This image contains the detailed table and required output.

CodePudding user response：

If you already know all separate values (that make that combined string), a simple option might be conditional aggregation:

Sample data:

SQL> with test (combined) as
  2    (select 'TD/DEMAT/FOREX/NR_PINS' from dual union all
  3     select 'FOREX/NR_PINS/DEMAT'    from dual union all
  4     select 'NR_PINS/MF/DEMAT/TD'    from dual
  5    ),

Separate values:

  6  separate as
  7    (select 'TD' val  from dual union all
  8     select 'DEMAT'   from dual union all
  9     select 'NR_PINS' from dual union all
 10     select 'FOREX'   from dual
 11    )

Finally:

 12  select t.combined,
 13    max(case when s.val = 'TD'      and instr(t.combined, s.val) > 0 then 1 else 0 end) td,
 14    max(case when s.val = 'DEMAT'   and instr(t.combined, s.val) > 0 then 1 else 0 end) demat,
 15    max(case when s.val = 'NR_PINS' and instr(t.combined, s.val) > 0 then 1 else 0 end) nr_pins,
 16    max(case when s.val = 'FOREX'   and instr(t.combined, s.val) > 0 then 1 else 0 end) forex
 17  from test t cross join separate s
 18  group by t.combined;

COMBINED                       TD      DEMAT    NR_PINS      FOREX
---------------------- ---------- ---------- ---------- ----------
FOREX/NR_PINS/DEMAT             0          1          1          1
TD/DEMAT/FOREX/NR_PINS          1          1          1          1
NR_PINS/MF/DEMAT/TD             1          1          1          0

SQL>

CodePudding user response：

You can use:

SELECT combined,
       CASE WHEN '/' || combined || '/' LIKE '%/TD/%' THEN 1 ELSE 0 END AS td,
       CASE WHEN '/' || combined || '/' LIKE '%/DEMAT/%' THEN 1 ELSE 0 END AS demat,
       CASE WHEN '/' || combined || '/' LIKE '%/NR_PINS/%' THEN 1 ELSE 0 END AS nr_pins,
       CASE WHEN '/' || combined || '/' LIKE '%/FOREX/%' THEN 1 ELSE 0 END AS forex
FROM   table_name

Note: you need to include the delimiters before and after the strings to match and being matched as, if you do not, you can match a partial item.

Which, for the sample data:

CREATE TABLE table_name (
  combined VARCHAR2(50),
  td       NUMBER(1,0),
  demat    NUMBER(1,0),
  nr_pins  NUMBER(1,0),
  forex    NUMBER(1,0)
);

INSERT INTO table_name (combined)
SELECT 'TD/DEMAT/FOREX/NR_PINS' FROM DUAL UNION ALL
SELECT 'FOREX/NR_PINS/DEMAT' FROM DUAL UNION ALL
SELECT 'NR_PINS/MF/DEMAT/TD' FROM DUAL UNION ALL
SELECT 'NOT_TD/NOT_DEMAT/NOT_NR_PINS/NOT_FOREX' FROM DUAL;

Outputs:

COMBINED TD DEMAT NR_PINS FOREX

TD/DEMAT/FOREX/NR_PINS 1 1 1 1

FOREX/NR_PINS/DEMAT 0 1 1 1

NR_PINS/MF/DEMAT/TD 1 1 1 0

NOT_TD/NOT_DEMAT/NOT_NR_PINS/NOT_FOREX 0 0 0 0

COMBINED	TD	DEMAT	NR_PINS	FOREX
TD/DEMAT/FOREX/NR_PINS	1	1	1	1
FOREX/NR_PINS/DEMAT	0	1	1	1
NR_PINS/MF/DEMAT/TD	1	1	1	0
NOT_TD/NOT_DEMAT/NOT_NR_PINS/NOT_FOREX	0	0	0	0

If you want to update the table then you can use a MERGE statement:

MERGE INTO table_name dst
USING (
  SELECT ROWID as rid,
         CASE WHEN '/' || combined || '/' LIKE '%/TD/%' THEN 1 ELSE 0 END AS td,
         CASE WHEN '/' || combined || '/' LIKE '%/DEMAT/%' THEN 1 ELSE 0 END AS demat,
         CASE WHEN '/' || combined || '/' LIKE '%/NR_PINS/%' THEN 1 ELSE 0 END AS nr_pins,
         CASE WHEN '/' || combined || '/' LIKE '%/FOREX/%' THEN 1 ELSE 0 END AS forex
  FROM   table_name
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET td = src.td,
      demat = src.demat,
      nr_pins = src.nr_pins,
      forex = src.forex;

db<>fiddle here