Re: HELP SQL GURUS
Date: 1996/12/19
Message-ID: <32B8FA97.1937_at_dircon.co.uk>#1/1
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)) desc7FROM brians_view
GROUP
BY id
/
Hope this is some use Received on Thu Dec 19 1996 - 00:00:00 CET