Don Dealy II wrote:
>
> Sample Table
>
> DocId Integer
> DateVal Date
> TextVal Varchar2(40)
> NumVal Number(10,0)
>
>
> Sample Data
>
> DocId DateVal TextVal NumVal
> 12 01/15/2000
> 12 Sally Smith
> 5 Bob Brown
> 5 48702
> 5 07/22/2001
> 4 152
>
>
>
> Desired result after SELECT...
>
> DocId DateVal TextVal NumVal
> 12 01/15/2000 Sally Smith
> 5 07/22/2001 Bob Brown 48702
> 4 152
>
>
> How do I write a query to produce the "single row" result?
>
>
>
> Thx - Don
>
> e: [EMAIL PROTECTED]
>
>
select DocId,
decode(max(nvl(dateval,
to_date('1/1/1066','MM/DD/YYYY'))),
to_date('1/1/1066', 'MM/DD/YYYY'), NULL,
max(nvl(dateval,
to_date('1/1/1066','MM/DD/YYYY')))) dateval,
decode(min(nvl(textval, 'ZZZZ')), 'ZZZZ', NULL,
min(nvl(textval, 'ZZZZ'))) textval,
decode(max(nvl(numval, -9999999999)),
-9999999999, NULL,
max(nvl(numval, -9999999999))) numval
from sample_table
group by docid
Untried, but should work.
--
Regards,
Stephane Faroult
Oriole Corporation
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
