Montei um SQL que agrega num string os projetos de uma pessoa e depois usando expressão regular extrai os projetos dos quais ela participou.
Você precisa ter Oracle versão 10g para funcionar esse SQL.
No exemplo abaixo no WHERE coloquei 3 REGEXP_LIKE, um para cada projeto que quero que a pessoa tenha participado (projetos 2, 3 e 4).
É só modificar ali dentro e colocar os números que você quiser. Se quiser que a pessoa tenha participado em 10 projetos, vai ter que adicionar 10 linhas no WHERE com 10 vezes copiado o REGEXP_LIKE mudando só o código do projeto.
Selecionar tudo
WITH pessoa_projetos AS
(SELECT 82 pessoa_id, 1 projeto_id FROM dual UNION ALL
SELECT 82, 5 FROM dual UNION ALL
SELECT 90, 5 FROM dual UNION ALL
SELECT 94, 2 FROM dual UNION ALL
SELECT 102, 1 FROM dual UNION ALL
SELECT 102, 2 FROM dual UNION ALL
SELECT 102, 3 FROM dual UNION ALL
SELECT 102, 4 FROM dual)
-- fim dos dados de exemplo
SELECT pessoa_id, lista_proj_pess, qtd_proj_pess
FROM (SELECT pessoa_id,
sys_connect_by_path(projeto_id, '\') lista_proj_pess,
LEVEL qtd_proj_pess
FROM (SELECT pp.*,
row_number() over(PARTITION BY pessoa_id ORDER BY projeto_id) num_proj_pess
FROM pessoa_projetos pp)
WHERE connect_by_isleaf = 1
START WITH num_proj_pess = 1
CONNECT BY PRIOR (pessoa_id) = pessoa_id
AND PRIOR (num_proj_pess) + 1 = num_proj_pess)
WHERE regexp_like(lista_proj_pess, '\\2\\*') -- coloque o código do projeto
AND regexp_like(lista_proj_pess, '\\3\\*') -- um código para cada linha
AND regexp_like(lista_proj_pess, '\\4\\*')
Segue abaixo exemplo da execução:
Selecionar tudo
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as fsitja
SQL>
SQL> WITH pessoa_projetos AS
2 (SELECT 82 pessoa_id, 1 projeto_id FROM dual UNION ALL
3 SELECT 82, 5 FROM dual UNION ALL
4 SELECT 90, 5 FROM dual UNION ALL
5 SELECT 94, 2 FROM dual UNION ALL
6 SELECT 102, 1 FROM dual UNION ALL
7 SELECT 102, 2 FROM dual UNION ALL
8 SELECT 102, 3 FROM dual UNION ALL
9 SELECT 102, 4 FROM dual)
10 -- fim dos dados de exemplo
11 SELECT pessoa_id, lista_proj_pess, qtd_proj_pess
12 FROM (SELECT pessoa_id,
13 sys_connect_by_path(projeto_id, '\') lista_proj_pess,
14 LEVEL qtd_proj_pess
15 FROM (SELECT pp.*,
16 row_number() over(PARTITION BY pessoa_id ORDER BY projeto_id) num_proj_pess
17 FROM pessoa_projetos pp)
18 WHERE connect_by_isleaf = 1
19 START WITH num_proj_pess = 1
20 CONNECT BY PRIOR (pessoa_id) = pessoa_id
21 AND PRIOR (num_proj_pess) + 1 = num_proj_pess)
22 WHERE regexp_like(lista_proj_pess, '\\2\\*')
23 AND regexp_like(lista_proj_pess, '\\3\\*')
24 AND regexp_like(lista_proj_pess, '\\4\\*')
25 /
PESSOA_ID LISTA_PROJ_PESS QTD_PROJ_PESS
---------- ---------------------- -------------
102 \1\2\3\4 4
SQL>
Abraços,
Francisco.