COLUMN INTO ROWS AND VICE VERSA [message #36186] |
Thu, 08 November 2001 21:12 |
Rajarshi Dasgupta
Messages: 52 Registered: October 2001
|
Member |
|
|
HOW CAN I MAKE COLUMNS INTO ROWS AND ROWS INTO COLUMNS FROM A TABLE INSIDE A PL/SQL BLOCK => UPDATE IN ANOTHER TABLE OR CREATE A VIEW BY THAT.
THANX.
----------------------------------------------------------------------
|
|
|
Re: COLUMN INTO ROWS AND VICE VERSA [message #36207 is a reply to message #36186] |
Fri, 09 November 2001 10:29 |
Siva Ram
Messages: 22 Registered: November 2001
|
Junior Member |
|
|
Hi Rajashri
It's very big code, if you are not able to see this properly, send an email to me and I'll be more than happy to send you the attachment.
CREATE OR REPLACE FUNCTION tabcount (
sch IN VARCHAR2,
tab IN VARCHAR2)
RETURN INTEGER
IS
/*
|| Dependencies:
|| DBMS_SQL -- Oracle dynamic SQL package
*/
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
ignore INTEGER;
retval INTEGER;
BEGIN
DBMS_SQL.PARSE (cur, 'SELECT COUNT(*) FROM ' || sch || '.' || tab,
DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, retval);
ignore := DBMS_SQL.EXECUTE_AND_FETCH (cur);
DBMS_SQL.COLUMN_VALUE (cur, 1, retval);
DBMS_SQL.CLOSE_CURSOR (cur);
RETURN retval;
END;
/
CREATE OR REPLACE PROCEDURE transpose (
source_owner IN VARCHAR2,
source_table IN VARCHAR2,
target_owner IN VARCHAR2,
target_table IN VARCHAR2,
showaction IN BOOLEAN := FALSE
)
IS
/*
|| Procedure to transpose a table (columns to rows,
|| rows to columns). In Oracle7, this means that the
|| table may not have more than 254 rows. In Oracle8,
|| the limit is raised to 1000 rows.
||
|| You must create the transpose table (target_table)
|| before you run this procedure. That way, you get to
|| determine the transposed column names.
||
|| Dependencies:
|| tabcount -- see above
|| DBMS_SQL -- Oracle dynamic SQL package
*/
rcount NUMBER := tabcount (source_owner, source_table);
cur INTEGER;
ignore INTEGER;
stmt VARCHAR2(32767);
prefix CHAR(1);
CURSOR colcur (
s_owner ALL_TAB_COLUMNS.owner%TYPE,
s_table ALL_TAB_COLUMNS.table_name%TYPE
)
IS
SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE owner = UPPER (s_owner)
AND table_name = UPPER (s_table)
ORDER BY column_id;
BEGIN
IF rcount > 0
THEN
cur := DBMS_SQL.OPEN_CURSOR;
FOR colrec IN colcur (source_owner, source_table)
LOOP
prefix := NULL;
stmt :=
'INSERT INTO ' || target_owner || '.' || target_table || '
SELECT ';
FOR rnum IN 1 .. rcount
LOOP
stmt :=
stmt ||
prefix ||
'MAX(DECODE(ROWNUM,' ||
rnum ||
',' ||
colrec.column_name ||
',NULL))';
IF rnum = 1
THEN
prefix := ',';
END IF;
END LOOP;
stmt := stmt || ' FROM ' || source_owner || '.' || source_table;
IF showaction
THEN
p.l (stmt);
END IF;
DBMS_SQL.PARSE (cur, stmt, DBMS_SQL.native);
ignore := DBMS_SQL.EXECUTE (cur);
IF showaction
THEN
p.l ('INSERT result', ignore);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END IF;
END;
/
Thank you and good luck !
Siva Ram
|
|
|