Have you tried a dynamic sql approach ?

Add this to a proc with a ref cursor param and return with open..for

Sam

------------------------------
SET serveroutput on

DECLARE
ods VARCHAR2( 4000 );
i INT;

CURSOR cur
IS
SELECT deptno
, dname
FROM dept;

cur_rec cur%ROWTYPE;
BEGIN
OPEN cur;

ods := 'select ';
i := 0;

LOOP
FETCH cur
INTO cur_rec;

EXIT WHEN cur%NOTFOUND;

IF ( cur_rec.deptno IS NOT NULL ) THEN
IF ( i > 0 ) THEN
ods := ods || ', ';
END IF;

ods := ods || cur_rec.deptno || ' ' || cur_rec.dname;
END IF;

i := i + 1;
END LOOP;

CLOSE cur;

ods := ods || ' from dual';
DBMS_OUTPUT.put_line( ods );

OPEN <REF CURSOR> FOR ods;
END;
/


------------------------------




-----Original Message-----
From: Mahesh Naralkar
[mailto:oracledba-ezmlmshield-x3953061.[Email address protected]
Sent: 27 July 2006 05:33
To: LazyDBA Discussion
Subject: Transpose Rows into columns

Hi,

I want to to transpose rows into columns dynamically.

For ex.

SQL> select deptno, dname from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

I want the result as
10 20 30 40
ACCOUNTING RESEARCH SALES OPERATIONS

whithout hardcoding the column names.

These row may get increased. As row get inserted a new column will also
get generated dynamically.

Suppose there are 50 rows the query will show me the 50 columns in
result

Regards,

Mahesh


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html


________________________________________________________________________
In order to protect our email recipients, Betfair use SkyScan from
MessageLabs to scan all Incoming and Outgoing mail for viruses.

________________________________________________________________________


Oracle LazyDBA home page