pivoting SQL result sets

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. Received on Thu Nov 17 2005 - 09:21:01 CST