Re: Help: a challanege SQL request
Date: Thu, 30 Jun 2011 15:42:39 -0700 (PDT)
On Jun 28, 3:50 pm, charles <dshprope..._at_gmail.com> wrote:
> 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?
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.
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jun 30 2011 - 17:42:39 CDT