Re: Query result: switching rows to columns

On Apr 24, 3:12 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On 24 abr, 08:33, Jeremy <jeremy0..._at_gmail.com> wrote:
>
>
>
>
>
> > If there is a feature available in 9i to help achieve the following I
> > would be grateful if you could point me to it.
>
> > Say we have data stored thus:
>
> > create table test_results
> > (person_id number,
> > q_id test_id number,
> > answer varchar2(30));
>
> > person_id is FK to person table
> > q_id is FK to questions table
>
> > Suppose we want to list all the answers given by all candisates for
> > question whose ID is 16,17 or 19
>
> > select person_id, q_id, answer
> > from test_results
> > where q_id in (16,17,19);
>
> > And we get a 3 column result set
>
> > person_id q_id answer
> > 1 17 42
> > 1 19 Yes
> > 2 16 Always
>
> > etc.
>
> > I believe it is possible but don't know what to look for to rewrite a
> > query like this so that is will place the different Q_ID values as
> > column headings:
>
> > i.e. what we would like to achieve is something like this
>
> > person_id q_id_16_a q_id_17_a q_id_19_a
> > 1 42 Yes
> > 2 Always
>
> > I am sure I recall seeing this kind of question asked before but cannot
> > think what it is called. Of course this could just be wishful thinking!
>
> > Thanks for your help.
>
> > --
> > jeremy
>
> > ============================================================
> > ENVIRONMENT:
> > Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> > ============================================================
>
> Go search 'PIVOT TABLE'
>
> Cheers.
>
> Carlos.- Hide quoted text -
>
> - Show quoted text -

Here is a link to a simple technique

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/232ba4b0028bd1f5/cdee1d79caf2c73a?lnk=st&q=%22pivot+table%22+group:comp.databases.oracle.*&rnum=5&hl=en#cdee1d79caf2c73a

You can also find example at http://asktom.oracle.com seach on pivot table

HTH -- Mark D Powell -- Received on Tue Apr 24 2007 - 15:14:15 CDT