RE: Displaying rows as column headers help
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.
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
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.
LizzReceived on Thu Jul 24 2008 - 22:48:55 CDT