0

The set of data from my current query

AcctNbr MIACCTTYPCD USRFLDCD VALUE BAL
10001 VALU PSMX Y 1500
10001 VALU PSOD 1 1500
10002 CHK PSMX Y 2000
10002 CHK PSOD 1 2000
10003 HPLS PSMX Y 3000
10003 HPLS PSOD 1 3000

The result I want to make

AcctNbr MIACCTTYPCD USRFLDCD VALUE BAL
10001 VALU PSMX, PSOD Y, 1 1500
10002 CHK PSMX, PSOD Y, 1 2000
10003 HPLS PSMX, PSOD Y, 1 3000

Query:

select a.acctnbr, a.MIACCTTYPCD, userfieldcd.USRFLDCD, userfieldcd.value, m.bal as odbal
from table a
left join table h on a.acctnbr = h.acctnbr and h.entity = 'REOD' and h.inactv is null and h.acctentityvalue = 'Y'
left join table b on a.acctnbr = b.acctnbr and b.lastmaint = to_date('2022-08-25','YYYY-MM-DD')
left join table m on a.acctnbr = m.acctnbr and baltypecode = 'MOVD',
(select u.acctnbr, u.USRFLDCD, u.value 
from table u
where u.USRFLDCD in ('PSMX', 'PSOD') 
) USERFIELDCD 
where a.ACCTCD = 'CK'
and a.curracctstatcd = 'ACT'
and m.bal <> 0
and USERFIELDCD.acctnbr = a.acctnbr
order by a.acctnbr;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
Jey10
  • 3
  • 1
  • 4
  • duplicate https://stackoverflow.com/questions/12145379/how-to-retrieve-two-columns-data-in-a-b-format-in-oracle – Mr_Thorynque Aug 25 '22 at 15:47
  • It should be something like this then? LISTAGG(USERFIELDCD.USRFLDCD, ', ') within group (ORDER BY USERFIELDCD.USRFLDCD) as Blah – Jey10 Aug 25 '22 at 16:08

0 Answers0