Re: Query challenge

qili_at_indiana.edu (Kenneth Li) wrote:
>
> Hi, there,,
>
> I have a table (call it Table 1) with the following structure:
>
> Fields: ID Month DelCat
>
> where ID is customer identification number, DelCat is the customer's
> delinquency status with values of 10, 15, 20 or 50 in month specified by Month.
>
> I would like to generate Table 2 like
>
> Fields: ID Ct10Mth Ct15Mth Ct20Mth Ct50Mth
>
> where ID is the customer id in Table 1, Ct10Mth is the *FIRST* month
> that customer was in DelCat 10; Ct15Mth is the *FIRST* month that customer
> was in DelCat 15, etc.

Try

select id,

       min( decode( delcat, 10, month, NULL ) ) Ct10Mth,
       min( decode( delcat, 15, month, NULL ) ) Ct15Mth,
       min( decode( delcat, 20, month, NULL ) ) Ct20Mth,
       min( decode( delcat, 50, month, NULL ) ) Ct50Mth

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

>
> I have not been able to find a query to generate such a table.
>
> Your help is appreciated.
> ==========================================================================
> Enjoy Life! | Internet: qili_at_indiana.edu
> --- Kenneth Li | Bitnet: qili_at_indiana.bitnet
>
>
> --
> ==========================================================================
> Enjoy Life! | Internet: qili_at_indiana.edu
> --- Kenneth Li | Bitnet: qili_at_indiana.bitnet
Received on Tue May 02 1995 - 00:00:00 CEST