Re: Query challenge

In article 799355663_at_copper, qili_at_indiana.edu (Kenneth Li) writes:
>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.
>
>I have not been able to find a query to generate such a table.
>

How about:

insert into table_2

  select ID, min(decode(DelCat,'10',Month,null)),
             min(decode(DelCat,'15',Month,null)),
             min(decode(DelCat,'20',Month,null)),
             min(decode(DelCat,'50',Month,null))

Note: The MIN function should ignore the null values returned by the decode.

      You may have to use a "to_char" function on the month column, depending
      on the actual values (is it of DATE or CHAR type?).

Tony Received on Mon May 01 1995 - 00:00:00 CEST