Subselect and concatenate the same Oracle column result

Question

I’m with that query, without the subselect she returns to me:

Cod_atendimento | cod_agrupamento| cod_material
11223.             2.              200
11223.             2.              300

Result I’m trying to:

Cod_atendimento | cod_agrupamento| cod_material
11223.             2.              200, 300

Query:

SELECT DISTINCT cod_atendimento,
COD_AGRUPAMENTO,
COALESCE(
(SELECT CAST(cd.cod_material AS VARCHAR(10)) + ';' AS [text()]
FROM 
PRESCRICAO_MEDICA_V ab, PRESCRICAO_SOLUCAO bc, PRESCRICAO_MATERIAL cd
where ab.cod_prescricao = bc.cod_prescricao
and bc.cod_prescricao = cd.cod_prescricao
and ab.cod_prescricao = 23214
and cod.ie_suspenso = 'N'
AND cd.COD_SEQUENCIA_SOLUCAO = bc.COD_SEQ_SOLUCAO
ORDER BY CODIGO
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') as materiais

from 
PRESCRICAO_MEDICA_V a, 
PRESCRICAO_SOLUCAO b, 
PRESCRICAO_MATERIAL c
where a.cod_prescricao = b.cod_prescricao
and b.cod_prescricao = c.cod_prescricao
and a.cod_prescricao = 23214
AND c.COD_SEQUENCIA_SOLUCAO = b.COD_SEQ_SOLUCAO
GROUP BY a.cod_atendimento, b.COD_AGRUPAMENTO

Answer 1

I suggest something like this:

I also suggest adopting the use of ANSI syntax for JOINS which, in addition to being common to most SQL implementations, makes reading easier (and has no implications for performance).

SELECT 
  cod_atendimento,
  cod_agrupamente
  LISTAGG(materiais, ',') WITHIN GROUP (ORDER BY materiais) AS materiais
FROM (
  SELECT DISTINCT 
    cod_atendimento,
    cod_agrupamente,
    COALESCE((SELECT CAST(cd.cod_material AS VARCHAR(10)) + ';' AS [text()]
              FROM prescricao_medica_v ab, prescricao_solucao bc, prescricao_material cd
              WHERE ab.cod_prescricao = bc.cod_prescricao
                AND bc.cod_prescricao = cd.cod_prescricao
                AND ab.cod_prescricao = 23214
                AND cod.ie_suspenso = 'N'
                AND cd.cod_sequencia_solucao = bc.cod_seq_solucao
              ORDER BY CODIGO
              FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), ''), '; ')
    ) as materiais
  FROM prescricao_medica_v a, prescricao_solucao b, prescricao_material c
  WHERE a.cod_prescricao = b.cod_prescricao
    AND b.cod_prescricao = c.cod_prescricao
    AND a.cod_prescricao = 23214
    AND c.cod_sequencia_solucao = b.cod_seq_solucao
)
GROUP BY cod_atendimento, cod_agrupamento;