Forum Stats

  • 3,836,755 Users
  • 2,262,182 Discussions
  • 7,900,095 Comments

Discussions

Every Row into an individual Columns in SQL Query

534103
534103 Member Posts: 288
edited May 15, 2010 9:40AM in SQL & PL/SQL
Hi Experts,
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production          
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE	11.1.0.7.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production                         
NLSRTL Version 11.1.0.7.0 - Production          
-- DROP TABLE RENDER_VALUES;
CREATE TABLE RENDER_VALUES
(
    ID          INTEGER PRIMARY KEY,
    FIELD_NAME  VARCHAR2(1000),
    FIELD_VALUE VARCHAR2(1000)
);
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(1,'CropX','10.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(2,'CropY','20.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(3,'Height','100');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(4,'Width','200');
COMMIT;
SELECT FIELD_NAME,FIELD_VALUE FROM RENDER_VALUES;
FIELD_NAME	FIELD_VALUE
CropX		10.31234
CropY		20.31234
Height		100
Width		200
I need the output as:
CropX		CropY		Height	Width
10.31234	20.31234	100	200
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?

- Dharan V

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,206 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
«1

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    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
    730428 Member Posts: 2,087
    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
    Karthick2003 Member Posts: 13,711 Bronze Badge
    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.
    Karthick2003
  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    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
    Frank Kulash Member, Moderator Posts: 42,206 Red Diamond
    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.

    */
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
    Frank Kulash
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    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
    746738 Member Posts: 1
    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
    534103 Member Posts: 288
    edited Jan 15, 2010 9:37AM
    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
    BluShadow Member, Moderator Posts: 42,107 Red Diamond
    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.
This discussion has been closed.