Re: Query result: switching rows to columns

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. Received on Tue Apr 24 2007 - 02:12:11 CDT