Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    46

    Unanswered: Complex Query Again

    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 FIELD1,
    SELECT FIELD2,
    (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.

    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Go to asktom.oracle.com and put in a search for stragg.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •