Re: How to return Multi-row query into a single row?

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 25 Jun 2001 01:38:15 -0700
Message-ID: <F001.00335ECE.20010625014524@fatcity.com>

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: dondealy_at_teleport.com
>
>

 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: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Jun 25 2001 - 03:38:15 CDT