Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 584 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 45 Data Integration
- 45 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 666 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Every Row into an individual Columns in SQL Query
Answers
-
Hi,
Here's an example using a VARCHAR2 varaible in PL/SQL.VARIABLE dj REFCURSOR CREATE OR REPLACE FUNCTION dept_by_job RETURN SYS_REFCURSOR IS -- dept_by_job returns a SYS_REFCURSOR that has -- one row for every department in scott.emp and one -- column for every job, showing the number of employees -- in that department with that job. return_csr SYS_REFCURSOR; select_txt VARCHAR2 (5000); BEGIN -- 1st part of query is fixed select_txt := 'SELECT deptno '; -- 2nd part of query depends on what's in the table FOR r IN ( SELECT DISTINCT job FROM scott.emp ORDER BY job ) LOOP select_txt := select_txt || ', COUNT (CASE WHEN job = ''' || r.job || ''' THEN 1 END) AS ' || r.job || '_cnt '; END LOOP; -- Last part of query is fixed select_txt := select_txt || ' FROM scott.emp' || ' GROUP BY deptno' || ' ORDER BY deptno'; OPEN return_csr FOR select_txt; RETURN return_csr; END dept_by_job ; / SHOW ERRORS EXEC :dj := dept_by_job; PRINT :dj
Or, since you provided a test table, here's an example tailored to your test table:CREATE OR REPLACE FUNCTION pivot_rv RETURN SYS_REFCURSOR IS return_csr SYS_REFCURSOR; select_txt VARCHAR2 (5000); BEGIN -- 1st part of query is fixed select_txt := 'SELECT '; -- 2nd part of query depends on what's in the table FOR r IN ( SELECT DISTINCT field_name , ROW_NUMBER () OVER (ORDER BY id) AS rnum FROM render_values ORDER BY rnum ) LOOP select_txt := select_txt || CASE WHEN r.rnum > 1 THEN ',' END || 'MAX (CASE WHEN field_name = ''' || r.field_name || ''' THEN field_value END) AS "' || r.field_name || '"'; END LOOP; -- Last part of query is fixed select_txt := select_txt || ' FROM render_values'; OPEN return_csr FOR select_txt; RETURN return_csr; END pivot_rv ;
Edited by: Frank Kulash on Jan 15, 2010 10:42 AM -
I like Pivot B-)
col CropX for a10 col CropY for a10 col Height for a10 col Width for a10 select * from RENDER_VALUES pivot(max(field_value) for (ID,FIELD_NAME) in(('1','CropX') as CropX, ('2','CropY') as CropY, ('3','Height') as Height, ('4','Width') as Width)); CROPX CROPY HEIGHT WIDTH -------- -------- ------ ----- 10.31234 20.31234 100 200
-
Thanks for spending your valuable time for defining me the exact thing what i need.
Thats gr8..thanks once again.
-Dharan V
Edited by: DharanV on Jan 16, 2010 12:08 PM -
Hi Blushadow,
>
Apart from the fact that WM_CONCAT is an undocumented function and should not be used in production code otherwise you will invalidate any support you have with Oracle.
>
I also likely to use WM_CONCAT function in my oracle 11.1.0.7.0 since my version does not support LISTAGG.
Do you say that i need to stop using WM_CONCAT then?
-Dharan V -
Ji, Dharan,DharanV wrote:Oracle is not promising that WM_CONCAT will be there in any future version, or that it will behave the same if it is (though in future versions you will want to use LISTAGG anyway.)
...
I also likely to use WM_CONCAT function in my oracle 11.1.0.7.0 since my version does not support LISTAGG.
Do you say that i need to stop using WM_CONCAT then?
Why not copy and install STRAGG from [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402]? It does the same thing as WM_CONCAT, and it will not disappear or change unless you make it. -
Frank Kulash wrote:Hi Frank,
Ji, Dharan,DharanV wrote:Oracle is not promising that WM_CONCAT will be there in any future version, or that it will behave the same if it is (though in future versions you will want to use LISTAGG anyway.)
...
I also likely to use WM_CONCAT function in my oracle 11.1.0.7.0 since my version does not support LISTAGG.
Do you say that i need to stop using WM_CONCAT then?
Why not copy and install STRAGG from [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402]? It does the same thing as WM_CONCAT, and it will not disappear or change unless you make it.
There were too many codes and functions. I too reviewed from top to bottom but not understood clearly.
Can u please provide me the exact code to work out this?
Thanks, -
Hi,DharanV wrote:Here's the code that was on AskTom, with the line numbers removed so you can run it. Is that what you need?
...
There were too many codes and functions. I too reviewed from top to bottom but not understood clearly.
Can u please provide me the exact code to work out this?CREATE OR REPLACE TYPE string_agg_type AS OBJECT ( total varchar2(4000), STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT string_agg_type ) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate ( self IN OUT string_agg_type , value IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate ( self IN string_agg_type , returnValue OUT VARCHAR2 , flags IN NUMBER ) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge ( self IN OUT string_agg_type , ctx2 IN string_agg_type ) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY string_agg_type IS STATIC FUNCTION ODCIAggregateInitialize ( sctx IN OUT string_agg_type ) RETURN NUMBER IS BEGIN sctx := string_agg_type ( NULL ); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateIterate ( self IN OUT string_agg_type, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN self.total := self.total || ',' || value; RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateTerminate ( self IN string_agg_type , returnValue OUT VARCHAR2 , flags IN NUMBER ) RETURN NUMBER IS BEGIN returnValue := LTRIM (self.total, ','); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateMerge ( self IN OUT string_agg_type , ctx2 IN string_agg_type ) RETURN NUMBER IS BEGIN self.total := self.total || ctx2.total; RETURN ODCIConst.Success; END; END; / SHOW ERRORS CREATE OR REPLACE FUNCTION stragg ( input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; / SHOW ERRORS
If you log in as my_schema and run the above code once, you'll be able to use STRAGG forever.
To allow everyone else to use it:CREATE PUBLIC SYNONYM stragg FOR my_schema.stragg; GRANT EXECUTE ON string_agg_type TO PUBLIC; GRANT EXECUTE ON stragg TO PUBLIC;
-
Thanks Frank,
I just changed the Member Function asMEMBER FUNCTION ODCIAggregateIterate ( self IN OUT string_agg_type, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN -- self.total := self.total || ',' || value; *self.total := SUBSTR(self.total || value, 1, 4000);* RETURN ODCIConst.Success; END;
To provide my own delimiter to stragg the output.
Thanks,
Dharan V -
Hi Experts,
When i tried to use Stragg Function inside package, it throws errorCREATE OR REPLACE PACKAGE PKG_TEST AS FUNCTION STRAGG(INPUT VARCHAR2) RETURN VARCHAR2; END PKG_TEST; / CREATE OR REPLACE PACKAGE BODY PKG_TEST AS FUNCTION STRAGG (INPUT VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; END PKG_TEST; / SHOW ERRORS Package created. Warning: compiled but with compilation errors
Any Suggestions...
Thanks
Edited by: DharanV on May 15, 2010 7:10 PM
Since this thread is answered, let me post my error in a new thread.
This discussion has been closed.