Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pivoting SQL result sets

Re: pivoting SQL result sets

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 17 Nov 2005 08:06:47 -0800
Message-ID: <1132243607.648811@yasure>


gerridge_at_att.net wrote:
> I'm working with a table of survey data in which each respondent has
> data for 2 questions: 1) what is your household type? (family, couple,
> roommate, single), and 2) how many people do you shop for?
> (1,2,3,4,5+). I'd like to pivot the result set of the following query
>
> select s.customer_id, sd.question, sd.answer
> from survey s, surveydata sd
> where s.survey_name = 'demo_survey'
> and s.id = sd.survey_id
> and sd.question in ('household type','number shop for')
>
> result set...
>
> CUSTOMER_ID QUESTION ANSWER
> 716031402 household type family with children
> 716031402 number shop for 3
> 716813838 household type single
> 716813838 number shop for 1
> 716554049 household type family with children
> 716554049 number shop for 5+
> 716609273 household type couple
> 716609273 number shop for 2
>
>
> ...into a matrix that returns the customer counts for each possible
> combination...
>
> 1 2 3 4 5+
> family with children 0 0 70 50 10
> couple 0 60 0 0 0
> roommate(s) 0 30 20 10 0
> single 40 0 0 0 0
>
>
> I've used decode statements to construct pivots in the past, but that
> doesn't seem to be an option in this case.
>
> Any suggestions as to how to best handle this would be greatly
> appreciated.
>
> Thanks, -Ddoug.
>

www.psoug.org
click on Morgan's Library
click on DECODE
There are many cross-tabulation demos

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Nov 17 2005 - 10:06:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US