Hello Tom,

I have a simple question - maybe you can help answer it.

I have a table of the type :

CREATE TABLE TEST
( USER_ID NUMBER NOT NULL ENABLE,
QUESTION_ID NUMBER NOT NULL ENABLE,
RESPONSE VARCHAR2(20 BYTE)
)

with the following sample data :

insert into test values (123,1,'Apple');
insert into test values (123,1,'Banana');
insert into test values (123,1,'Mango');
insert into test values (123,2,'Dog');
insert into test values (124,1,'Grape');
insert into test values (124,2,'Cat');
insert into test values (124,2,'Dolphin')
insert into test values (125,1,'Orange')
insert into test values (125,3,'USA')



This is intended to hold the response to a survey.
The questions may have multiple answers and some questions may be optional (i.e. no answer).

I need to generate a view of the table which lists the responses of a user in one row. Multiple answers should be separated by a comma.
This is intended to be a easy to read survey summary.

e.g. for the sample data the output should look something like :

USERS QUESTION_1 QUESTION_2 QUESTION_3
123 Apple,Banana,Mango Dog -
124 Grape Cat, Dog -
125 Orange - USA


I spent some time trying to come up with a query to this, but did not get the required output. Ended up using a java program written by my colleague.

I would like to know if it is possible to generate the desired output using a single SQL query. Thanks for your help!

Best,
Gaurav

ops$tkyte%ORA10GR2> select user_id,
  2         max(decode(question_id,1,data)) q1,
  3         max(decode(question_id,2,data)) q2,
  4         max(decode(question_id,3,data)) q3,
  5         max(decode(question_id,4,data)) q4
  6    from (
  7  select user_id,
  8         question_id,
  9         substr(max( sys_connect_by_path( response, ', ' ) ),2) data
 10    from (select test.*, row_number() over (partition by user_id, question_id order by response) rn
 11            from test)
 12  start with rn = 1
 13  connect by prior user_id = user_id and prior question_id = question_id and prior rn = rn-1
 14  group by user_id, question_id
 15         )
 16  group by user_id
 17  order by user_id
 18  /

   USER_ID Q1                    Q2                    Q3                    Q4
---------- --------------------- --------------------- --------------------- -----
       123  Apple, Banana, Mango  Dog
       124  Grape                 Cat, Dolphin
       125  Orange                                      USA