Effectuer le comptage du nombre d'occurrences dans SQL

Cela peut être fait avec union all et row_number():

select id,  row_number() over(partition by id order by seq) couting, name
from (
    select id, name1 name, 1 seq from mytable where cond1 = 'Yes'
    union all select id, name2, 2 from mytable where cond2 = 'Yes'
    union all select id, name3, 3 from mytable where cond3 = 'Yes'
    union all select id, name4, 4 from mytable where cond4 = 'Yes'
    union all select id, name5, 5 from mytable where cond5 = 'Yes'
) x
order by id, rn

Vous pouvez utiliser CONNECT BY LEVEL pour obtenir le résultat souhaité comme suit:

SELECT
    ID,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LVL) AS "Counting",
    NAME_   AS "Name"
FROM
    (SELECT
            T.ID,
            DECODE(LVL, 1, COND1, 2, COND2, 3, COND3, 4, COND4, 5, COND5) AS COND,
            DECODE(LVL, 1, NAME1, 2, NAME2, 3, NAME3, 4, NAME4, 5, NAME5) AS NAME_,
            LVL   AS LVL
        FROM
            YOUR_TABLE T join 
       (Select level as lvl from dual CONNECT BY  LEVEL <= 5) on (1=1)
     )
WHERE COND = 'Yes';

Cheers !!

Une autre option:

SQL> with
  2  test (id, cond1, cond2, cond3, cond4, cond5, name1, name2, name3, name4, name5) as
  3    -- your sample data
  4    (select 1, 'no' , 'yes', 'no' , 'no', 'yes', null     , 'result1', 'n/a'    , null, 'result2' from dual union all
  5     select 2, 'yes', 'no' , 'yes', 'no', 'yes', 'result3', 'n/a'    , 'result4', null, 'result5' from dual
  6    ),
  7  temp as
  8    -- values whose COND column is 'yes'
  9    (select id,
 10       decode(cond1, 'yes', name1) n1,
 11       decode(cond2, 'yes', name2) n2,
 12       decode(cond3, 'yes', name3) n3,
 13       decode(cond4, 'yes', name4) n4,
 14       decode(cond5, 'yes', name5) n5
 15     from test
 16    ),
 17  up as
 18    -- unpivot data
 19    (select *
 20     from temp
 21     unpivot (c_name for pc in (n1, n2, n3, n4, n5))
 22    )
 23    -- final result
 24  select id,
 25         row_number() over (partition by id order by c_name) counting,
 26         c_name as name
 27  from up
 28  order by id;

        ID   COUNTING NAME
---------- ---------- -------
         1          1 result1
         1          2 result2
         2          1 result3
         2          2 result4
         2          3 result5

SQL>

Voici une autre option utilisant UNPIVOT.

ID COUNTING NAMES
1  1        Result1
1  2        Result2
2  1        Result3
2  2        Result4
2  3        Result5

Résultat:

with ns as (
select id,
       n,
       names 
  from mytab
unpivot(names for n in (name1 as 'n1', 
                        name2 as 'n2', 
                        name3 as 'n3',
                        name4 as 'n4',
                        name5 as 'n5'))),
cs as (
select id,
       n,
       condns 
  from mytab
unpivot(condns for n in (cond1 as 'n1', 
                         cond2 as 'n2', 
                         cond3 as 'n3',
                         cond4 as 'n4', 
                         cond5 as 'n5')))
select ns.id, 
       row_number() over(partition by ns.id order by ns.n) counting, 
       ns.names 
  from ns inner join cs
    on ns.id = cs.id 
   and ns.n = cs.n 
   and cs.condns = 'Yes'
 order by 1,2;

Solution basée sur UNPIVOT.

ID COND1 COND2 COND3 COND4 COND5 NAME1   NAME2   NAME3   NAME4    NAME5
1  No    Yes   No    No    Yes   (null)  Result1 n/a     (null)   Result2
2  Yes   No    Yes   No    Yes   Result3 n/a     Result4 (null)   Result5

Sortie :

create table mytab(id number, 
                   cond1 varchar2(3), 
                   cond2 varchar2(3), 
                   cond3 varchar2(3), 
                   cond4 varchar2(3), 
                   cond5 varchar2(3), 
                   Name1 varchar2(7),
                   Name2 varchar2(7), 
                   Name3 varchar2(7), 
                   Name4 varchar2(7), 
                   Name5 varchar2(7));

insert into mytab values(1,'No','Yes','No','No','Yes',null,'Result1','n/a',null,'Result2');

insert into mytab values(2,'Yes','No','Yes','No','Yes','Result3','n/a','Result4',null,'Result5');

commit;

select * from mytab;

Vous pouvez utiliser UNPIVOT avec des paires de colonnes, puis filtrer sur les lignes Yes et utiliser la fonction analytique ROW_NUMBER pour obtenir l'index incrémentiel du résultat:

Requête :

ID | COUNT | NAME   
-: | ----: | :------
 1 |     1 | Result1
 1 |     2 | Result2
 2 |     1 | Result3
 2 |     2 | Result4
 2 |     3 | Result5

Données de test :

CREATE TABLE table_name (
  ID    NUMBER(10,0) PRIMARY KEY,
  Cond1 VARCHAR2(3) CHECK ( Cond1 IN ( 'Yes', 'No' ) ),
  Cond2 VARCHAR2(3) CHECK ( Cond2 IN ( 'Yes', 'No' ) ),
  Cond3 VARCHAR2(3) CHECK ( Cond3 IN ( 'Yes', 'No' ) ),
  Cond4 VARCHAR2(3) CHECK ( Cond4 IN ( 'Yes', 'No' ) ),
  Cond5 VARCHAR2(3) CHECK ( Cond5 IN ( 'Yes', 'No' ) ),
  Name1 VARCHAR2(10),
  Name2 VARCHAR2(10),
  Name3 VARCHAR2(10),
  Name4 VARCHAR2(10),
  Name5 VARCHAR2(10),
  CHECK ( ( Cond1 = 'Yes' AND Name1 IS NOT NULL ) OR ( Cond1 = 'No' AND ( Name1 IS NULL OR Name1 = 'n/a' ) ) ),
  CHECK ( ( Cond2 = 'Yes' AND Name2 IS NOT NULL ) OR ( Cond2 = 'No' AND ( Name2 IS NULL OR Name2 = 'n/a' ) ) ),
  CHECK ( ( Cond3 = 'Yes' AND Name3 IS NOT NULL ) OR ( Cond3 = 'No' AND ( Name3 IS NULL OR Name3 = 'n/a' ) ) ),
  CHECK ( ( Cond4 = 'Yes' AND Name4 IS NOT NULL ) OR ( Cond4 = 'No' AND ( Name4 IS NULL OR Name4 = 'n/a' ) ) ),
  CHECK ( ( Cond5 = 'Yes' AND Name5 IS NOT NULL ) OR ( Cond5 = 'No' AND ( Name5 IS NULL OR Name5 = 'n/a' ) ) )
);

INSERT INTO table_name ( ID, Cond1, Cond2, Cond3, Cond4, Cond5, Name1, Name2, Name3, Name4, Name5 )
SELECT 1, 'No',  'Yes', 'No',  'No', 'Yes', null,      'Result1', 'n/a',     null, 'Result2' FROM DUAL UNION ALL
SELECT 2, 'Yes', 'No',  'Yes', 'No', 'Yes', 'Result3', 'n/a',     'Result4', null, 'Result5' FROM DUAL;