Home » RDBMS Server » Server Administration » Does Oracle SQL has crosstab query using transform statement like in Access
|
|
| Re: Does Oracle SQL has crosstab query using transform statement like in Access [message #373042 is a reply to message #373025] |
Fri, 23 March 2001 12:37   |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
from www.revealnet.com/plsql-pipeline
CREATE OR REPLACE FUNCTION tabcount (
sch IN VARCHAR2,
tab IN VARCHAR2)
RETURN INTEGER
IS
/*
|| Generic function utilizing dynamic SQL to return the
|| number of rows in the specified table.
||
|| Author: Steven Feuerstein
|| Source: PL/SQL Pipeline (www.revealnet.com/plsql-pipeline)
||
|| 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.
||
|| Author: Solomon Yakobson
|| Source: PL/SQL Pipeline (www.revealnet.com/plsql-pipeline)
||
|| 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;
/
CREATE TABLE orig_tab (
name VARCHAR2(30),
hobby VARCHAR2(100),
age NUMBER,
birthdate DATE
);
CREATE TABLE trans_tab (
steven VARCHAR2(500),
larry VARCHAR2(500),
solomon VARCHAR2(500)
);
INSERT INTO orig_tab VALUES ('Steven', 'Soccer Referee', '40', '23-SEP-58');
INSERT INTO orig_tab VALUES ('Larry', 'Domination', '54', '17-AUG-44');
INSERT INTO orig_tab VALUES ('Solomon', 'Pipeline', '20', '15-AUG-78');
BEGIN
transpose (USER, 'orig_tab', USER, 'trans_tab');
END;
/
SELECT * FROM trans_tab;
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 06:15:27 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
