Oracle 将列转换为行

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

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   MRK6

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