RE: Displaying rows as column headers help
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <lizzpenaorclgrp@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Jul 2008 23:48:55 -0400
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:
