Re: HELP SQL GURUS

Brian Spears wrote:
>
> Hi,
>
> I have a simple join on two table to produce a/view or table
> with the following rows.
>
> id Desc
> 88 a
> 88 b
> 88 c
> 90 a
> 90 b
> 91 d
>
> Under time pressure I have to build a view to TRANSPOSE
> the rows and colums like this
>
> id desc1 desc2 desc3 ... desc7
> 88 a b c NULL
> 90 a b NULL NULL
> 91 d NULL NULL NULL
>
> There is a maximum of only using seven columns.
>

[Quoted] The nearest I can get is a view which will give you:

      id  desc1  desc2  desc3  desc4   ...   desc7
      88   a      b      c      NULL        NULL
      90   a      b      NULL   NULL        NULL
      91  NULL   NULL    NULL    d          NULL

i.e. desc1 will only contain a or NULL, desc2 b or NULL, etc.

Here goes:

CREATE OR REPLACE
VIEW timbos_view
AS
SELECT id,

       MIN (DECODE (desc, 'a', desc, NULL)) desc1,
       MIN (DECODE (desc, 'b', desc, NULL)) desc2,
       MIN (DECODE (desc, 'c', desc, NULL)) desc3,
       MIN (DECODE (desc, 'd', desc, NULL)) desc4,
       MIN (DECODE (desc, 'e', desc, NULL)) desc5,
       MIN (DECODE (desc, 'f', desc, NULL)) desc6,
       MIN (DECODE (desc, 'g', desc, NULL)) desc7

Hope this is some use Received on Thu Dec 19 1996 - 00:00:00 CET