Re: Help: a challanege SQL request

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Thu, 30 Jun 2011 15:42:39 -0700 (PDT)
Message-ID: <59d50682-314c-44ba-aba1-c9c3d2fe8c0f_at_g9g2000yqb.googlegroups.com>

On Jun 28, 3:50 pm, charles <dshprope..._at_gmail.com> wrote:
> All,
>
> I have a table like this:
> COL1            COL2
> ------------------------------
> I                     a
> I                     b
> I                     c
> II                    a
> II                    b
> III                   a
> III                   b
> III                   c
>
> I would like to get all the col1 that data entry is the same as I.
>
> So in this case, I has a, b, c,  and III has a, b, c as well.  I would
> like to get III, but II.
>
> Could somebody help?
>
> Thanks

If you are running a recent release of Oracle Database, take a look at the LISTAGG function. See the example here: http://hoopercharles.wordpress.com/2011/05/26/row-values-to-comma-separated-lists-an-overly-complicated-use-case-example/

LISTAGG will allow you to collapse the multiple row values from column COL2 for each distinct value in column COL1, into a single row. Once the multiple rows are collapsed to a single row the final processes should be fairly straight-forward.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jun 30 2011 - 17:42:39 CDT