asked on

LISTAGG in Oracle for joined table and if there is no data in the column.

hi All,
Using LISTAGG i am trying to achieve the below in Oracle.

Billrun Account
C1 12345
C1
C1 67894
C2 23567
C2
C2 15566

I want to return, if there is no data i need to get and empty space.
C1 12345, ,67894
C2 23567, ,15566

But i am getting always like
C1 12345,67894
C2 23567,15566

Jan Louwerens

In your WHERE clause, add: AND Account IS NOT NULL

ASKER CERTIFIED SOLUTION

Jan Louwerens

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vinoy K P

ASKER

Thanks For the reply
But its making some issue in the order
Table Data
BANGSTESTCASE      DAVID        III
Bangstestcase      Michelle      123

OutPut after Query Execution- 123 is supposed to come after III
DAVID,Michelle      BANGSTESTCASE,Bangstestcase      123, III

Query i am using
SELECT BO.LOAN_ID ,
LISTAGG(BO.BORROWER_FIRST_NAME, ',') WITHIN GROUP (ORDER BY BO.BORROWER_FIRST_NAME) AS FIRST_NAME,
LISTAGG(BO.BORROWER_LAST_NAME, ',') WITHIN GROUP (ORDER BY BO.BORROWER_LAST_NAME) AS LAST_NAME,
LISTAGG(NVL(TO_CHAR(BO.BORROWER_SUFFIX), ' '), ', ') WITHIN GROUP (ORDER BY BO.BORROWER_SUFFIX) AS BORROWER_SUFFIX
FROM WCTS.BORROWER BO RIGHT OUTER JOIN WCTS.CO_BORROWER COB ON BO.LOAN_ID = COB.LOAN_ID WHERE BO.LOAN_ID = 1
--AND BO.BORROWER_SUFFIX IS NOT NULL - This wont work because i will loose the entire data if SUFFIX is NULL
GROUP BY BO.LOAN_ID;

Vinoy K P

ASKER

Thanks it works , I Understood that the problem was with my Order By i Changed it to
order by rownum and all looks good . Thanks for the help.