Re: Displaying rows as column headers help
- From: "Roman Podshivalov" <roman.podshivalov@xxxxxxxxx>
- To: kennaim@xxxxxxxxx
- Date: Fri, 25 Jul 2008 14:01:42 -0400
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))
ORDER BY product;
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@xxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *laura pena
> *Sent:* Thursday, July 24, 2008 4:38 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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
>
>
>
>
>
>
>
Other related posts:
