Path: g2news2.google.com!postnews.google.com!g9g2000yqb.googlegroups.com!not-for-mail
From: Charles Hooper <hooperc2...@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Help: a challanege SQL request
Date: Thu, 30 Jun 2011 15:42:39 -0700 (PDT)
Organization: http://groups.google.com
Lines: 41
Message-ID: <59d50682-314c-44ba-aba1-c9c3d2fe8c0f@g9g2000yqb.googlegroups.com>
References: <ce8be703-c386-42a7-8593-7969990e55e7@a31g2000vbt.googlegroups.com>
NNTP-Posting-Host: 75.241.116.213
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1309473883 2641 127.0.0.1 (30 Jun 2011 22:44:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 30 Jun 2011 22:44:43 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: g9g2000yqb.googlegroups.com; posting-host=75.241.116.213; posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64;
 Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR
 3.0.30729; Media Center PC 6.0; .NET4.0C; GTB7.0),gzip(gfe)

On Jun 28, 3:50=A0pm, charles <dshprope...@gmail.com> wrote:
> All,
>
> I have a table like this:
> COL1 =A0 =A0 =A0 =A0 =A0 =A0COL2
> ------------------------------
> I =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 a
> I =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 b
> I =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 c
> II =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0a
> II =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0b
> III =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 a
> III =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 b
> III =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 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, =A0and III has a, b, c as well. =A0I woul=
d
> 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.