SELECT TO_CHAR(A.COLLECTION_DATE,'DD-MON-YY') COL_DATE, B.AGENT_NAME, SUM(A.AMOUNT) AMOUNT
FROM DAILY_COLLECTION A
INNER JOIN DAILY_COLLECTION_AGENTS B USING (AGENT_CODE)
GROUP BY A.COLLECTION_DATE, B.AGENT_NAME
ORDER BY A.COLLECTION_DATE;

COL_DATE

AGENT_NAME

AMOUNT

31-JAN-19

PETER

18000

31-JAN-19

RAVI

48500

01-FEB-19

RAVI

13000

02-FEB-19

PETER

6000

02-FEB-19

RAVI

3000

I can achieve this using below query. But problem is, AGENT_NAME is hardcoded. Tomorrow, if a new agent introduced, I need to change the script. Number of Agents will be < 5. Over the year, there may be 1 or 2 increase.

SELECT TO_CHAR(COLLECTION_DATE,'DD-MON-YY') COL_DT, SUM(PETER) PETER, SUM(RAVI) RAVI
FROM (
SELECT COLLECTION_DATE,
CASE WHEN AGENT_NAME = 'PETER' THEN AMOUNT END PETER,
CASE WHEN AGENT_NAME = 'RAVI' THEN AMOUNT END RAVI
FROM DAILY_COLLECTION
)
GROUP BY COLLECTION_DATE
ORDER BY COLLECTION_DATE;

And what would you do with such query if you don't know number of columns it returns? Use it as a report? Then use reporting tools - every reporting tool can do that. Other than that - use SQL*Plus to generate SQL and then run it using SQL*Plus substitution variables:

Yes, we can use LISTAGG here instead of XMLAGG I used (I overlooked number of agents is < 5). But keep in mind LISTAGG is not a generic solution since it returns VARCHAR2 and therefore is limited to 4000 bytes while pivoting FOR clause is limited to 1000 expressions in which case just quotes and commas LISTAGG will use 2999 bytes (first quote, 999 quote-comma-quote and last quote) leaving only 1001 bytes for agent names which leaves us with 1 character per agent name even when using single byte character code.

I can achieve this using below query. But problem is, AGENT_NAME is hardcoded. Tomorrow, if a new agent introduced, I need to change the script. Number of Agents will be < 5. Over the year, there may be 1 or 2 increase.

Even if I use PIVOT, hard-coding is needed. Is there a way to achieve this without hard-coding of Agent_name? Tomorrow if a new agent is added, the code has to work without any modifications.

And what would you do with such query if you don't know number of columns it returns?

Yes, you are right. That answers my question !! If I am using pl/sql script to generate output report, then I can use the PIVOT solution you mentioned. However, if I am building a report in APEX for example, the whole concept is irrelevant. While creating a report on APEX, we need to know column details in advance. So, even though, there is a solution available at pl/sql level, it is of no use for APEX report, because, report template is already built. Modification in report is still needed, for new columns to appear in that report.

Even, I checked the thread No. 5 by Salim which is also in the similar lines.

And what would you do with such query if you don't know number of columns it returns?

Yes, you are right. That answers my question !! If I am using pl/sql script to generate output report, then I can use the PIVOT solution you mentioned. However, if I am building a report in APEX for example, the whole concept is irrelevant. While creating a report on APEX, we need to know column details in advance. So, even though, there is a solution available at pl/sql level, it is of no use for APEX report, because, report template is already built. Modification in report is still needed, for new columns to appear in that report.

Even, I checked the thread No. 5 by Salim which is also in the similar lines.

Thanks every one for your valuable inputs.

Best Regards,

-Anand

However, if you created a view on your data which pivots it with known column names then you can use that in your Apex reports as you would build your apex form around those columns. The form would also need an additional query to list (perhaps at the side) which column relates to which "agent", for the user to reference.

However, if I am building a report in APEX for example, the whole concept is irrelevant.

I don't have much APEX experience but I believe pivot data within interactive report is available starting APEX 5. Or you can simply write PL/SQL function that returns pivot SQL tailored to number of agents - same as my SQL*Plus example, just no need for XMLAGG (or LISTAGG) - simply loop through distinct agent names. Then call it in APEX to pass generated SQL to report.