Re: Query result: switching rows to columns

Jeremy 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.

www.psoug.org
Click on Morgan's Library
Click on DECODE
Look at the cross-tabulation demos

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org