Sugestão para resolução de Select.

[burgaParticipante]

Hoje me inspirei pra quebrar um pouco a cabeça com esse problema… A resposta, escrevendo somente em SQL é esta:

WITH TAB1 AS
(SELECT CODPROCESSO,
NOME,
CPF,
VALOR,
ROW_NUMBER() OVER (PARTITION BY CODPROCESSO ORDER BY NOME) X
FROM USUARIO
),
TAB2 AS
(SELECT T.*, MAX(X) OVER (PARTITION BY CODPROCESSO) Y FROM TAB1 T
),
TAB3 AS
(SELECT CODPROCESSO,
TRIM(LEADING ',' FROM C1) NOME_AGRUPADO,
TRIM(LEADING ',' FROM C2) CPF_AGRUPADO,
TRIM(LEADING ',' FROM C3) VALOR_AGRUPADO,
FROM TAB2
MODEL
PARTITION BY (CODPROCESSO)
DIMENSION BY (X)
MEASURES (1 I, NOME, CPF, VALOR, Y,
CAST(NULL AS VARCHAR2(4000)) AS C1,
CAST(NULL AS VARCHAR2(4000)) AS C2,
CAST(NULL AS VARCHAR2(4000)) AS C3)
RULES ITERATE (10000) UNTIL (I[1] > Y[1])
( C1[1] = C1[1] || ',' || NOME,
C2[1] = C2[1] || ',' || CPF,
C3[1] = C3[1] || ',' || VALOR,
I[1] = I[1] + 1 )
)
SELECT * FROM TAB3 WHERE NOME_AGRUPADO IS NOT NULL;

Sei que é um pouco tarde e você já deve ter resolvido seu problema de outra forma, mas estou postando só pra deixar registrado aqui se outras pessoas precisarem…

A mesma consulta na tabela HR.EMPLOYEES pra testarem:

WITH TAB_TEXTO AS (
SELECT MANAGER_ID, FIRST_NAME, JOB_ID,
ROW_NUMBER() OVER (PARTITION BY MANAGER_ID ORDER BY EMPLOYEE_ID) X
FROM HR.EMPLOYEES
WHERE MANAGER_ID IS NOT NULL),
TAB_TEXTO2 AS (
SELECT T.*, MAX(X) OVER (PARTITION BY MANAGER_ID) Y
FROM TAB_TEXTO T),
TAB_TEXTO3 AS (
SELECT MANAGER_ID,
TRIM(LEADING ',' FROM C1) C1,
TRIM(LEADING ',' FROM C2) C2
FROM TAB_TEXTO2
MODEL
PARTITION BY (MANAGER_ID)
DIMENSION BY (X)
MEASURES (FIRST_NAME, JOB_ID, Y,
CAST(NULL AS VARCHAR2(4000)) AS C1,
CAST(NULL AS VARCHAR2(4000)) AS C2)
RULES ITERATE (100) UNTIL (ITERATION_NUMBER+1 >= Y[1])
( C1[1] = C1[1] || ',' || FIRST_NAME[ITERATION_NUMBER+1],
C2[1] = C2[1] || ',' || JOB_ID[ITERATION_NUMBER+1]
))
SELECT * FROM TAB_TEXTO3 WHERE C1 IS NOT NULL;

Abraços,