Re: Displaying rows as column headers help

in 11g you can try new pivot clause:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/analysis.htm#DWHSG0209

SELECT * FROM
  (SELECT product, channel, amount_sold
   FROM sales_view
   ) S PIVOT (SUM(amount_sold)
   FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,

                   5 AS CATALOG_SALES, 9 AS TELESALES))

PRODUCT                  DIRECT­_SALES  INTERNET_SALES  CATALOG_SALES  TELESALES
----------------------   ------------  --------------  -------------  ---------
...
Internal 6X CD-ROM          229512.97        26249.55
Internal 8X CD-ROM          286291.49        42809.44
Keyboard Wrist Rest         200959.84        38695.36                   1522.73

--romas

On 7/24/08, Ken Naim <kennaim_at_gmail.com> wrote:
>
> This will depend on if the number of values, and the values themselves
> are known in advance in which case you could use the max and case functions
> to filter and transpose the data. If not which I assume is the case, then
> you will need to process through the locus column (either using and array or
> a loop) and then building a second query using dynamic sql to build the
> query using max and case functions as the number of values are now known. I
> believe asktom.oralce.com has a few example of the dynamic queries that
> accomplish this, search for "transpose rows columns" or something along that
> line. He might even have a package that already accomplishes it if memory
> serves and I'm sure it would be more robust than something I would put
> together on the fly this late at night. If you can't find it, I'll put an
> example together for you in the morning.
>
>
>
> Ken Naim
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *laura pena
> *Sent:* Thursday, July 24, 2008 4:38 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* SQL: Displaying rows as column headers help
>
>
>
> So I have the following requirement:
>
>
>
> CODE COL_ID SPECIES FISH_ID LOCUS ALLELES
> chintest 11 23220 1 Omm1080v1 252,268
> chintest 11 23220 1 Oki10v1 183,241
> chintest 11 23220 1 christian 252,268
> chintest 11 23220 2 Omm1080v1 256,284
> chintest 11 23220 2 Oke4v1 245,245
> chintest 11 23220 2 Oki10v1 187,225
>
>
> output to look like Locus should be shown columns wise with Alleles under
> the locus name:
> CODE COLID SPE FISH_ID Omm1080v1 Oki10v1 christian Omm1080v1
> Oke4v1
> chintest 11 23220 1 252,268
> chintest 11 23220 1 183,241
> chintest 11 23220 1
> 252,268
> chintest 11 23220
> 2 256,284
> chintest 11 23220
> 2
> 245,245
> chintest 11 23220 2 187,225
>
>
>
>
>
>
>
> Any ideas how to (even a start would be good) write this query? Any help
> is as always appreciated.
>
>
>
> Thanks,
>
> Lizz
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l