Skip to Main Content

Hardware

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle SPARC Architecture 2015 Draft D1.0.9

steph-choyer-OracleAug 12 2016 — edited Jan 6 2017

Comments

21205
there is one real solution: fix your datamodel....

seems like an EAV model, and that's a bad idea ^tm^

when it's available again, read this: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
730428
You can acheive this using [Pipelined table functions|http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm].

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/10/crittografia-in-plsql-utilizzando-dbms_crypto/]
Karthick2003
Just for sample ..i have given Four Values in Field name Column...there can be (n) no.of values in that and to get each row
into an individual columns...how can i do something like this?
(n) - Number of rows must be known in prior. Reason for that is in SQL Select the column in the select list must be defined while building it. If you know the number of row in advance you can do this.
 
select max(decode(id,1,field_value)) cropx, 
       max(decode(id,2,field_value)) cropy, 
       max(decode(id,3,field_value)) height, 
       max(decode(id,4,field_value)) width 
  from RENDER_VALUES 
If you don't now the rows in advance you can well write a dynamic procedure to give you a SELECT statement as above. But here the problem would be you will not know the number of column this dynamic query will be returning. So storing the result set or processing would be difficult.
Twinkle
Hi,

Since you are using Oracle 11g, you can use pivot feature.
[Pivot and Unpivot|http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html] .



Twinkle
Frank Kulash
Hi,

As karthick pointed out, the number of columns in a result set must be hard-coded into the query.
If you want to write something today that will handle however many columns you may need tomorrow, then use Dynamic SQL, where a preliminary query writes part of your query (the part with the unknown number of columns and their names) for you.

The exam[ple below uses a SQL*Plus SPOOL file to store the variable part of the query, but it can be adapted to use a substitution variable, a PL/SQL string variable, or something else.
{code}
/*
How to Pivot a Table with a Dynamic Number of Columns

This works in any version of Oracle
The "SELECT ... PIVOT" feature introduced in Oracle 11
is much better for producing XML output.

Say you want to make a cross-tab output of
the scott.emp table.
Each row will represent a department.
There will be a separate column for each job.
Each cell will contain the number of employees in
a specific department having a specific job.
The exact same solution must work with any number
of departments and columns.
(Within reason: there's no guarantee this will work if you
want 2000 columns.)

Case 0 "Basic Pivot" shows how you might hard-code three
job types, which is exactly what you DON'T want to do.
Case 1 "Dynamic Pivot" shows how get the right results
dynamically, using SQL*Plus.
(This can be easily adapted to PL/SQL or other tools.)

NOTE: Using SQL*Plus file I/O, as in this example, is just
one of many ways to do dynamic SQL.
*/


PROMPT ========== 0. Basic Pivot ==========

SELECT deptno
, COUNT (CASE WHEN job = 'ANALYST' THEN 1 END) AS analyst_cnt
, COUNT (CASE WHEN job = 'CLERK' THEN 1 END) AS clerk_cnt
, COUNT (CASE WHEN job = 'MANAGER' THEN 1 END) AS manager_cnt
FROM scott.emp
WHERE job IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY deptno
ORDER BY deptno
;


PROMPT ========== 1. Dynamic Pivot ==========

-- ***** Start of dynamic_pivot.sql *****

-- Suppress SQL*Plus features that interfere with raw output
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL p:\sql\cookbook\dynamic_pivot_subscript.sql

SELECT DISTINCT
', COUNT (CASE WHEN job = '''
|| job
|| ''' ' AS txt1
, 'THEN 1 END) AS '
|| job
|| '_CNT' AS txt2
FROM scott.emp
ORDER BY txt1;

SPOOL OFF

-- Restore SQL*Plus features suppressed earlier
SET FEEDBACK ON
SET PAGESIZE 50

SPOOL p:\sql\cookbook\dynamic_pivot.lst

SELECT deptno
@@dynamic_pivot_subscript
FROM scott.emp
GROUP BY deptno
ORDER BY deptno
;

SPOOL OFF

-- ***** End of dynamic_pivot.sql *****

/*
EXPLANATION:
The basic pivot assumes you know the number of distinct jobs,
and the name of each one. If you do, then writing a pivot query
is simply a matter of writing the correct number of ", COUNT ... AS ..."\
lines, with the name entered in two places on each one. That is easily
done by a preliminary query, which uses SPOOL to write a sub-script
(called dynamic_pivot_subscript.sql in this example).

The main script invokes this sub-script at the proper point.
In practice, .SQL scripts usually contain one or more complete
statements, but there's nothing that says they have to.
This one contains just a fragment from the middle of a SELECT statement.

Before creating the sub-script, turn off SQL*Plus features that are
designed to help humans read the output (such as headings and
feedback messages like "7 rows selected.", since we do not want these
to appear in the sub-script.
Turn these features on again before running the main query.

*/
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
Boneist
Alex Nuijten wrote:
there is one real solution: fix your datamodel....

seems like an EAV model, and that's a bad idea ^tm^

when it's available again, read this: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
I second everything Alex has said!
MichaelS
You may be interested in the thread How to pipeline a function with a dynamic number of columns? (in particular Anton's (ascheffer) solution).
746738
Dears,
I Think This Would Be Very Useful.



SQL> SELECT REPLACE (WMSYS.WM_CONCAT(EMPNO),',',' ') FROM EMP
2 /

REPLACE(WMSYS.WM_CONCAT(EMPNO),',','')
--------------------------------------------------------------------------------
7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7369 7499
7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
534103
Hi Frank,

It is quite interesting to go this way...but not clear with Scripts.

Can u please give me a sample with PL/SQL block ?. Just an sample code with dynamic variable in pl/sql for above specified example.

Edited by: DharanV on Jan 15, 2010 8:06 PM
BluShadow
user8995424 wrote:
Dears,
I Think This Would Be Very Useful.



SQL> SELECT REPLACE (WMSYS.WM_CONCAT(EMPNO),',',' ') FROM EMP
2 /

REPLACE(WMSYS.WM_CONCAT(EMPNO),',','')
--------------------------------------------------------------------------------
7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7369 7499
7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
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.

From 11g onwards there is a LISTAGG analytical function that does aggregation of lists and also allows partitioning and ordering of those lists (unlike WM_CONCAT).

Please do not make posts recommending undocumented functions.
Frank Kulash
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
Marked as Answer by 534103 · Sep 27 2020
Aketi Jyuuzou
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
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
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
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
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
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
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
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.
1 - 19

Post Details