Hi
I have a table called t_criteria with the columns "criterion_1", "criterion_2" and criterion_3" with the row entry for each column being either "Yes", "No" or "Not Known". (see DDL below)
What I want is a statement which will give the criteria numbers which have been met (i.e. row entry "Yes") in a comma separated list in a column called “criteria_met”.
I know how to do it in excel but not sure how I would go about it in oracle?
This is a way to do it in excel:
'=SUBSTITUTE(TRIM(IF(B2="Yes",1,"")&" "&IF(C2="Yes",2,"")&" "&IF(D2="yes",3,""))," ",",")
Where column B is criterion_1, column C is criterion_2 and column D is criterion_3.
I’m using database 11g r2
create table t_criteria
(site_id number(2) not null,
criterion_1 varchar2(25),
criterion_2 varchar2(25),
criterion_3 varchar2(25),
constraint site_id_pk primary key (site_id)
);
-----------------
insert into t_criteria values (1, 'Yes', 'No', 'Yes');
insert into t_criteria values (2, 'Yes', 'No', 'Not Known');
insert into t_criteria values (3, 'Yes', 'No', 'Yes');
insert into t_criteria values (4, 'No', 'Yes', 'Not Known');
insert into t_criteria values (5, 'No', 'Not Known', 'Not Known');
insert into t_criteria values (6, 'No', 'No', 'Yes');
insert into t_criteria values (7, 'No', 'Not Known', 'Not Known');
insert into t_criteria values (8, 'Yes', 'No', 'Not Known');
insert into t_criteria values (9, 'Yes', 'No', 'Not Known');
insert into t_criteria values (10, 'Yes', 'No', 'Not Known');
insert into t_criteria values (11, 'No', 'No', 'Not known');