Forum Stats

  • 3,853,311 Users
  • 2,264,203 Discussions
  • 7,905,317 Comments

Discussions

Every Row into an individual Columns in SQL Query

2»

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Feb 2, 2010 10:53AM Answer ✓
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • 534103
    534103 Member Posts: 288
    edited Jan 16, 2010 1:38AM
    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
  • 534103
    534103 Member Posts: 288
    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    Ji, Dharan,
    DharanV wrote:
    ...
    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?
    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.)

    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.
  • 534103
    534103 Member Posts: 288
    Frank Kulash wrote:
    Ji, Dharan,
    DharanV wrote:
    ...
    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?
    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.)

    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.
    Hi Frank,

    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,
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Feb 2, 2010 11:05AM
    Hi,
    DharanV wrote:
    ...
    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?
    Here's the code that was on AskTom, with the line numbers removed so you can run it. Is that what you need?
    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;
  • 534103
    534103 Member Posts: 288
    Thanks Frank,

    I just changed the Member Function as
        MEMBER 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
  • 534103
    534103 Member Posts: 288
    edited May 15, 2010 9:40AM
    Hi Experts,

    When i tried to use Stragg Function inside package, it throws error
    CREATE 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.