Re: pivoting SQL result sets

Maxim: Sorry -- the ambiguity in the result set was unintentional. The responses are always always consistent so pivoting the data is possible. And the method you proposed works perfectly. Thank you very much,

-DDoug

Maxim Demenko wrote:
> gerridge_at_att.net schrieb:
> > 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.
> >
>
> You know, to pivot the table you have to know exactly, which data you
> have ( in your example it was a little bit ambigous - possible values
> for household type were defined as (family, couple,roommate, single)
> but in example of result set it was "family with children")
> I built my example on your result set.
>
>
> SQL> CREATE TABLE surveydata
> 2 (customer_id NUMBER,
> 3 question VARCHAR2(30),
> 4 answer VARCHAR2(30));
>
> Table created.
>
> SQL>
> SQL> insert into surveydata values(
> 2 716031402,'household type','family with children'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716031402,'number shop for','3'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716813838,'household type','single'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716813838,'number shop for','1'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716554049,'household type','family with children'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716554049,'number shop for','5+'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716609273,'household type','couple'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716609273,'number shop for','2'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716031403,'household type','couple'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716031403,'number shop for','2'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716813839,'household type','couple'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716813839,'number shop for','5+'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716554050,'household type','family with children'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716554050,'number shop for','5+'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716609274,'household type','couple'
> 3 );
>
> 1 row created.
>
> SQL> insert into surveydata values(
> 2 716609274,'number shop for','2'
> 3 );
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> col householdtype for a20
> SQL>
> SQL> SELECT
> 2 householdtype,
> 3 nvl(COUNT(shopfor_1),0) shopfor_1,
> 4 nvl(COUNT(shopfor_2),0) shopfor_2,
> 5 nvl(COUNT(shopfor_3),0) shopfor_3,
> 6 nvl(COUNT(shopfor_4),0) shopfor_4,
> 7 nvl(COUNT(shopfor_5),0) shopfor_5
> 8 FROM (
> 9 SELECT CUSTOMER_ID,
> 10 MAX(DECODE(QUESTION, 'household type', ANSWER, NULL))
> HOUSEHOLDTYPE,
> 11 MAX(DECODE(QUESTION,
> 12 'number shop for',
> 13 DECODE(ANSWER, '1', 1, NULL),
> 14 NULL)) SHOPFOR_1,
> 15 MAX(DECODE(QUESTION,
> 16 'number shop for',
> 17 DECODE(ANSWER, '2', 1, NULL),
> 18 NULL)) SHOPFOR_2,
> 19 MAX(DECODE(QUESTION,
> 20 'number shop for',
> 21 DECODE(ANSWER, '3', 1, NULL),
> 22 NULL)) SHOPFOR_3,
> 23 MAX(DECODE(QUESTION,
> 24 'number shop for',
> 25 DECODE(ANSWER, '4', 1, NULL),
> 26 NULL)) SHOPFOR_4,
> 27 MAX(DECODE(QUESTION,
> 28 'number shop for',
> 29 DECODE(ANSWER, '5+', 1, NULL),
> 30 NULL)) SHOPFOR_5
> 31 FROM SURVEYDATA
> 32 GROUP BY CUSTOMER_ID)
> 33 GROUP BY householdtype
> 34 ;
>
> HOUSEHOLDTYPE SHOPFOR_1 SHOPFOR_2 SHOPFOR_3 SHOPFOR_4 SHOPFOR_5
> -------------------- ---------- ---------- ---------- ---------- ----------
> family with children 0 0 1 0 2
> single 1 0 0 0 0
> couple 0 3 0 0 1
>
> 3 rows selected.
>
>
> Best regards
>
> Maxim
Received on Thu Nov 17 2005 - 11:34:40 CST