RE: Displaying rows as column headers help

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