Skip to Main Content
  • Questions
  • Excel Line Break Using Oracle LISTAGG

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Denilson da Silva Guedes.

Asked: July 04, 2019 - 11:43 am UTC

Last updated: July 09, 2021 - 3:57 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked


I made a select Oracle clause containing several columns and one of them using the LISTAGG function, whose separator is a comma. The rows retrieved from this query will be listed on an excel worksheet, and the data from the previously cited column must contain line breaks immediately after the comma in the same cell. I used chr (10) as a separator in the LISTAGG function and did not result in the line break. Is there a solution that works?

and Chris said...

Line breaks in Windows are CR LF. Which are ASCII characters 13 and 10 respectively.

So you probably need:

listagg ( ..., chr(13) || chr(10) )

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Denilson da Silva Guedes Deni, July 04, 2019 - 3:19 pm UTC

I did not mention a detail previously, I am executing the select clause through the TOAD tool, retrieving the selected data from the clipboard and copying it to an excel sheet. Even using chr (13) || chr (10) as a separator in the LISTAGG function, did not result in line break within the same cell of the excel worksheet.
Follows the query executed in TOAD.

SELECT P.CDINSCRICAOIMOB || '-' || P.NUDIGITOINSCRICAOIMOB AS CDINSCRICAOIMOB,
(SELECT TRIM(PC.NUPROCESSO) || ' - ' || RP.DSRESULTADOPROCESSO
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.RESULTADOPROCESSO RP ON RP.CDRESULTADOPROCESSO = PC.STRESULTADO
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS NUPROCESSO2014,
(SELECT CFC.VLVARIAVELCALCULOOLD
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.CADFINANCCOMPLEMENTAR CFC ON CFC.STCARACTERIZACAO = PC.STCARACTERIZACAO
AND CFC.NUPROCESSO = PC.NUPROCESSO
AND CFC.AAEXERCICIO = PC.AAEXERCICIO
AND TRIM(CFC.SGVARIAVELCALCULO) = 'IPTU'
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS VLIPTUIMPUGNACAOOLD,
(SELECT CFC.VLVARIAVELCALCULONEW
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.CADFINANCCOMPLEMENTAR CFC ON CFC.STCARACTERIZACAO = PC.STCARACTERIZACAO
AND CFC.NUPROCESSO = PC.NUPROCESSO
AND CFC.AAEXERCICIO = PC.AAEXERCICIO
AND TRIM(CFC.SGVARIAVELCALCULO) = 'IPTU'
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS VLIPTUIMPUGNACAONEW,
(SELECT RC.VLRESULTADOCALCULO
FROM DBASAT.RESULTADOCALCULO RC
WHERE RC.AAEXERCICIO = 2014
AND RC.NUBASE = P.CDINSCRICAOIMOB
AND RC.NUFILIAL = 0
AND RC.CDTIPOCONTRIBUINTE = 'I'
AND RC.SGOBJETOCALCULO = 'IPTU') AS VLIPTU,
(SELECT CC.VLLANCAMENTOORIGINAL
FROM DBASAT.CONTACONTRIBUINTE CC
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS VLDIVIDAIPTUDIRETO,
(SELECT LISTAGG(LC.CDHISTORICO || ' - ' || trim(to_char(LC.VLLANCAM, '999G999G999G9999G999D99')), ', ') WITHIN GROUP (ORDER BY LC.SQLANCAMENTOCONTA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.LANCAMENTOCONTA LC ON LC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
AND LC.NUPARCELA IS NULL
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS DSLANCAMENTOSIPTUDIRETO,
(SELECT LISTAGG(trim(to_char(PARC.NUPARCELA, '00')) || ' - ' || trim(to_char(PARC.VLPARCELA, '999G999G999G9999G999D99')) || ' - ' || trim(to_char(PARC.DTVENCIMENTOPARCELA, 'dd/mm/yyyy')) || ' - ' || PARC.STPARCELA, chr(13) || chr(10)) WITHIN GROUP (ORDER BY PARC.NUPARCELA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.PARCELA PARC ON PARC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS DSCOTASIPTUDIRETO,
(SELECT CC.VLLANCAMENTOORIGINAL
FROM DBASAT.CONTACONTRIBUINTE CC
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS VLDIVIDAIPTUPROCESSO,
(SELECT LISTAGG(LC.CDHISTORICO || ' - ' || trim(to_char(LC.VLLANCAM, '999G999G999G9999G999D99')), ', ') WITHIN GROUP (ORDER BY LC.SQLANCAMENTOCONTA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.LANCAMENTOCONTA LC ON LC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
AND LC.NUPARCELA IS NULL
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS DSLANCAMENTOSIPTUPROCESSO,
(SELECT LISTAGG(trim(to_char(PARC.NUPARCELA, '00')) || ' - ' || trim(to_char(PARC.VLPARCELA, '999G999G999G9999G999D99')) || ' - ' || trim(to_char(PARC.DTVENCIMENTOPARCELA, 'dd/mm/yyyy')) || ' - ' || PARC.STPARCELA, chr(13) || chr(10)) WITHIN GROUP (ORDER BY PARC.NUPARCELA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.PARCELA PARC ON PARC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS DSCOTASIPTUPROCESSO
from DBASAT.PROPRIEDADE p
where P.CDINSCRICAOIMOB in (637985,
637986,
637987,
637988,
637989,
637990,
637991,
637992,
637993,
637994,
637995,
637996,
637997,
637998,
637999,
638000,
638001,
638002,
638003,
638004,
638006,
638007,
638008,
638009,
638010,
638011,
638012,
638013,
638014,
638015,
638016,
638017,
638018,
638019,
638020,
638021,
638022,
638023,
638024,
638025,
638026,
638027,
638028,
638029,
638030,
638032,
638033,
638034,
638035,
638036,
638037,
638038,
638039,
638040,
638041,
638042,
638043,
638044,
638045,
638046,
638047,
638048,
638049,
638050,
638051,
638052,
638053,
638054,
638055,
638056,
638057,
645827,
679080,
679082,
679083,
679084,
679085,
679086,
679089,
679091,
679099,
679103,
679107,
679114,
679126,
679165,
679166,
679167,
679168,
679169,
679170,
679172,
679173,
679175,
679176,
679177,
679179,
679180,
679181,
679197,
679199,
679200,
679201,
679202,
679203,
679205,
679207,
679209,
679210,
679211,
679213,
679216,
679218,
679221,
679223)
ORDER BY P.CDINSCRICAOIMOB

export doesn't keep the line break in the cell but copy and paste does

James Clegg, July 08, 2021 - 12:39 pm UTC

I found that both
listagg(........,','||chr(10))  

and l
istagg(..., ','||chr(13)||chr(10))
gave me the line breaks within the cell when I copied and pasted from sql developer, but didn't when I saved the results as excel.
Connor McDonald
July 09, 2021 - 3:57 am UTC

Is this from TOAD or other?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.