Re: pivoting SQL result sets

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 17 Nov 2005 17:41:57 +0100
Message-ID: <437cb321$0$21959$9b4e6d93@newsread2.arcor-online.net>

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 - 10:41:57 CST