Ok, I just learned how to do levels and partitioning in an Oracle query today, and I have to say it’s pretty cool. Basically, I have a view that returns groups of data; i.e. multiple rows with matching “key” attributes but different non-prime attributes.

Simplistic example:

------------------------------------------------------
3786       01/26/2008      1        3      0578
------------------------------------------------------
3786       01/27/2008      1        3      0579
------------------------------------------------------
3786       01/28/2008      1        3      0581
------------------------------------------------------
3787       01/29/2008      1        3      0587
------------------------------------------------------
3787       01/30/2008      1        3      0591
------------------------------------------------------

I was wracking my brain trying to figure out how, with one query, I could properly aggregate this data into one resultset. Using OVER PARTITION BY, SYS_CONNECT_BY_PATH, and the LEVEL Pseudocolumn, I came up with a pretty slick resultset that groups certain columns from multiple rows into one parent row.

SELECT column1,
   ltrim(column2, '<br/>') column2,
   column3,
   column4,
   ltrim(column5, '<br/>') effective_date
FROM (SELECT row_number() over(PARTITION BY column1 ORDER BY column1, ord_level DESC) aggregateRow,
        column1,
        column2,
        column3,
        column4,
        column5
      FROM (SELECT column1,
                  column3,
                  column4,
                  LEVEL ord_level,
                  sys_connect_by_path(column2, '<br/>') column2,
                  sys_connect_by_path(column5, '<br/>') column5
           FROM (SELECT column1,
                       column2,
                       column3,
                       column4,
                       column5
                       row_number() over(PARTITION BY column1 ORDER BY column1, mapped_facility_no) dataSet
                   FROM   database_table
                   ORDER  BY column1, column3) x_alias
            CONNECT BY column1 = PRIOR column1
            AND dataSet - 1 = PRIOR dataSet
           )
    )
WHERE  aggregateRow = 1
ORDER  BY column1

Resultset:

------------------------------------------------------
          01/26/2008                     0578
3786      01/27/2008      1       3      0579
          01/28/2008                     0581
------------------------------------------------------
3787      01/29/2008      1       3      0587
          01/30/2008                     0591
------------------------------------------------------