I cannot use a pivot table as pivot tables can only be dynamic if you use a little bit of programming. I need to use a SQL statement and it would look something like the following:
(SELECT MAX(LAST_NAME || ' ' || FIRST_NAME) AS ALL_AGENTS
FROM ATTIC.V_GS_AGENT WHERE ATTIC.V_GS_AGENT.COMMID = COMMIS1.COMMID
GROUP BY ATTIC.V_GS_AGENT.COMMID)
FROM ATTIC.L3D2_CR_INST_PARTS_COMMIS COMMIS1
LEFT JOIN ATTIC.L3D_PARTICIPANT CUSTOMERS1
ON (COMMIS1.COMMID = CUSTOMERS1.COMMID AND
COMMIS1.PARTID = CUSTOMERS1.ID)
LEFT JOIN ATTIC.V_GS_SENTENCE TAXES1
ON (COMMIS1.COMMID = TAXES1.COMMID
AND TAXES1.PARTID = TAXES1.PARTID)
LEFT JOIN ETC ETC.
--- SOME SUB QUERY DOWN HERE.
The kind of code that I am looking for is a function that would collapse 4 or any number of rows into 1 row which would be delimitted by something.
I need a function that would work for collapsing a field of type varchar into one row. My code does work with using MAX or MIN on the 3rd field.