Hi,
I have a requirement to implement the following:
Create table marker(code varchar2(4),sid integer (10),mrk1 char(4),mrk2 char(4),mrk3 char(4),mrk4 char(4),mrk5 char(4),mrk6 char(4))
Create table candidate (code varchar2(4),sid integer(10))
Create table candidate_marker(code varchar2(4),sid integer(10),mrk char(4))
Eg: data
Marker:
Abcd 12345678 0 A B Dp Null Null
Sdfg 3456218 A B C Dr Dp Null
Ftgh 56784932 Null 1 T Null Null Dp
Yuio 7896543 T Null G Null Null Null
INSERT INTO MARKER VALUES (‘Abcd’,12345678,’0’,’A’,’B’,’DP’,NULL,NULL);
INSERT INTO MARKER VALUES (‘Sdfg’, 3456218,’A’,’B’,’C’,’DR’,’DP’,NULL);
INSERT INTO MARKER VALUES(‘Ftgh’, 56784932,NULL,’1’,’T’,NULL,NULL,’DP’);
INSERT INTO MARKER VALUES(‘Yuio’, 7896543,’T’,NULL,’G’,NULL,NULL,NULL);
Candidate:
Abcd 12345678
Sdfg 3456218
Ftgh 56784932
Yuio 7896543
INSERT INTO CANDIDATE VALUES(‘Abcd’, 12345678);
INSERT INTO CANDIDATE VALUES(‘Sdfg’, 3456218);
INSERT INTO CANDIDATE VALUES(‘Ftgh’, 56784932);
INSERT INTO CANDIDATE VALUES(‘Yuio’, 7896543);
The candidate marker should look like this:
Abcd 12345678 H0
Abcd 12345678 CA
Abcd 12345678 TB
Abcd 12345678 DP
Sdfg 3456218 HA
Sdfg 3456218 CB
Sdfg 3456218 TC
Sdfg 3456218 DR
Sdfg 3456218 DP
Ftgh 56784932 DEF
Ftgh 56784932 C1
Ftgh 56784932 TT
Ftgh 56784932 DP
Yuio 7896543 HT
Yuio 7896543 DEF1
Yuio 7896543 TG
To explain the logic, The candidate and marker tables will be joined on code and sid. From the marker table, the first three markers are mandatory and last three markers are optional. All the mandatory markers should be fixed with a letter and if any value is null, it should be replaced with a default value. From the optional markers, only DP and DR need to be populated in the candidate_marker table.
Please could some one help me in implementing this.
Thank you
You just need to do the unpivot! This will convert the columns to rows for you. Use the "include nulls" clause to display the columns with a null value.
Create table marker(code varchar2(4),sid integer,mrk1 char(4),mrk2 char(4),mrk3 char(4),mrk4 char(4),mrk5 char(4),mrk6 char(4));
Create table candidate (code varchar2(4),sid integer);
Create table candidate_marker(code varchar2(4),sid integer,mrk char(4));
INSERT INTO MARKER VALUES ('Abcd',12345678,'0','A','B','DP',NULL,NULL);
INSERT INTO MARKER VALUES ('Sdfg', 3456218,'A','B','C','DR','DP',NULL);
INSERT INTO MARKER VALUES('Ftgh', 56784932,NULL,'1','T',NULL,NULL,'DP');
INSERT INTO MARKER VALUES('Yuio', 7896543,'T',NULL,'G',NULL,NULL,NULL);
INSERT INTO CANDIDATE VALUES('Abcd', 12345678);
INSERT INTO CANDIDATE VALUES('Sdfg', 3456218);
INSERT INTO CANDIDATE VALUES('Ftgh', 56784932);
INSERT INTO CANDIDATE VALUES('Yuio', 7896543);
select * from marker
unpivot include nulls (
val for col in (mrk1, mrk2, mrk3, mrk4, mrk5, mrk6)
);
CODE SID COL VAL
Abcd 12345678 MRK1 0
Abcd 12345678 MRK2 A
Abcd 12345678 MRK3 B
Abcd 12345678 MRK4 DP
Abcd 12345678 MRK5
Abcd 12345678 MRK6
Sdfg 3456218 MRK1 A
Sdfg 3456218 MRK2 B
Sdfg 3456218 MRK3 C
Sdfg 3456218 MRK4 DR
Sdfg 3456218 MRK5 DP
Sdfg 3456218 MRK6
Ftgh 56784932 MRK1
Ftgh 56784932 MRK2 1
Ftgh 56784932 MRK3 T
Ftgh 56784932 MRK4
Ftgh 56784932 MRK5
Ftgh 56784932 MRK6 DP
Yuio 7896543 MRK1 T
Yuio 7896543 MRK2
Yuio 7896543 MRK3 G
Yuio 7896543 MRK4
Yuio 7896543 MRK5
Yuio 7896543 MRK6You can then use case expressions to select a default for the rows as needed, based on the source column. Then filter out those where this expression is null, for example:
select m.*,
case
when col = 'MRK1' then coalesce(val, 'DEF1')
when col = 'MRK2' then coalesce(val, 'DEF2')
else 'ETC'
end vals_with_defs
from marker
unpivot include nulls (
val for col in (mrk1, mrk2, mrk3, mrk4, mrk5, mrk6)
) m
where case
when col = 'MRK1' then coalesce(val, 'DEF1')
when col = 'MRK2' then coalesce(val, 'DEF2')
else 'ETC'
end is not null;
CODE SID COL VAL VALS_WITH_DEFS
Abcd 12345678 MRK1 0 0
Abcd 12345678 MRK2 A A
Abcd 12345678 MRK3 B ETC
Abcd 12345678 MRK4 DP ETC
Abcd 12345678 MRK5 ETC
Abcd 12345678 MRK6 ETC
Sdfg 3456218 MRK1 A A
Sdfg 3456218 MRK2 B B
Sdfg 3456218 MRK3 C ETC
Sdfg 3456218 MRK4 DR ETC
Sdfg 3456218 MRK5 DP ETC
Sdfg 3456218 MRK6 ETC
Ftgh 56784932 MRK1 DEF1
Ftgh 56784932 MRK2 1 1
Ftgh 56784932 MRK3 T ETC
Ftgh 56784932 MRK4 ETC
Ftgh 56784932 MRK5 ETC
Ftgh 56784932 MRK6 DP ETC
Yuio 7896543 MRK1 T T
Yuio 7896543 MRK2 DEF2
Yuio 7896543 MRK3 G ETC
Yuio 7896543 MRK4 ETC
Yuio 7896543 MRK5 ETC
Yuio 7896543 MRK6 ETC
You can read more about unpivoting at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot