转:Oracle的行转列2种方法来自Asktom的例子.

You Asked
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, Dolphin-
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

and we said...
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

 

you do need to know the maximum number of question_ids for this to be possible in SQL. You could query that out in one query and dynamically construct this query if need be.

If you are in 11g R2 or above , this is one of the way to produce the desired result .

 

WITH v_test AS
  (SELECT user_id ,
    question_id ,
    listagg(response , '~') within GROUP (
  ORDER BY response ) lst
  FROM test
  GROUP BY user_id ,
    question_id
  )
SELECT *
FROM
  (SELECT user_id , lst ,question_id FROM v_test
  )pivot(MAX(lst) FOR question_id IN (1 ,2 , 3 ))

 

If the number of questions ( question_ids) are not known at run time , you can use pivot for xml ....

原贴链接:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425