Pivot query

Hello, I am on 9i and have a question on pivot queries. This is my table data

Code:

---

QUESTION  SCORE
------------------       
1        0
2        1
3        0
4        1
5        0

---

and the output should be (based on the score):

Code:

---

CORRECT_ANSWER          WRONG_ANSWER
-----------------------------------------
2,4                        1,3,5

---

Can I achieve this in a single query ?

Thanks.

I am currently using a function to return the data in the required format and calling that function in my SQL.

Will be good if it is possible to do within the query itself....

Thanks.

I tried to use dynamic SQL within a PL/SQL procedure to re-create materialized view. Because the SQL statement for the creation is long, I store these mv name, tablespace, refresh method, refresh key and SQL select statement in a local table called xxx_mv_information. Then I create procedure with dynamic SQL in. The procudure was compiled successfully. When I run the procedure, it always give me ORA-00905 missing key word error. I have carefully check the syntax and can not find the problems. Please help me to figure out where the problem is. Thanks a lot in advance. Here is the procedure I created.

CREATE OR REPLACE PROCEDURE sp_recreat_mvs
IS
v_schema user_mviews.owner%type;
v_sql_string varchar2(1000);
v_sql_stmt varchar2(2000);

BEGIN

SELECT DISTINCT OWNER
INTO v_schema
FROM USER_MVIEWS;


SELECT TABSPACE||' '||REF_METHOD||' '||REF_KEY||' '||SQL_STRING
INTO v_sql_string
FROM XXX_MV_INFORMATION
ORDER BY MV_NUM;

FOR x IN (SELECT MV_NAME FROM XXX_MV_INFORMATION ORDER BY MV_NUM)

LOOP

v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||x.mv_name||v_sql_string;

EXECUTE IMMEDIATE v_sql_stmt;

END LOOP;

COMMIT;

END;

Search for the keyword stragg in google.

some sample urls are

http://www.williamrobertson.net/documents/one-row.html
http://laurentschneider.blogspot.com...-in-10gr2.html

Regards

Raj