Home » SQL & PL/SQL » SQL & PL/SQL » Create Dynamic Matrix View
Create Dynamic Matrix View [message #498150] Tue, 08 March 2011 23:20 Go to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Hi Friends,

I have create a solution for dynamic matrix view. Please go thru the below mentioned scripts

Creation of table
drop table SALES_matrix_sethu;

CREATE TABLE SALES_matrix_sethu
(
  EMPLOYEE     VARCHAR2(15 BYTE),
  Mmm          VARCHAR2(3 BYTE),
  Mm           VARCHAR2(3 BYTE),
  SALES_UNITS  NUMBER
);


Creation of Data
SET DEFINE OFF;
SET DEFINE OFF;
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Sethu', 'Jan', '01', 10);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Sethu', 'Feb', '02', 20);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Raji', 'Jan', '01', 25);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Raji', 'Feb', '02', 5);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Raji', 'Mar', '03', 30);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Kiran', 'Feb', '02', 10);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Sasi', 'Mar', '03', 50);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Shanmugam', 'Feb', '02', 5);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Shanmugam', 'Mar', '03', 2);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Kalyani', 'Jan', '01', 10);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Kalyani', 'Feb', '02', 15);
Insert into SALES_matrix_sethu
   (EMPLOYEE, MMM, MM, SALES_UNITS)
 Values
   ('Kalyani', 'Mar', '03', 25);
COMMIT;

Data after insertion
EMPLOYEE	MMM	MM	SALES_UNITS

Sethu	        Jan	01	10
Sethu	        Feb	02	20
Raji	        Jan	01	25
Raji	        Feb	02	 5
Raji	        Mar	03	30
Kiran	        Feb	02	10
Sasi	        Mar	03	50
Shanmugam	Feb	02	 5
Shanmugam	Mar	03	 2
Kalyani	        Jan	01	10
Kalyani	        Feb	02	15
Kalyani	        Mar	03	25

-- Creation of procedure to create matrix view
CREATE OR REPLACE procedure SALES_matrix_view_cren  is


v_month varchar(3);
v_mm   varchar(2);
v_view_sql_qry   long;

CURSOR SALES_matrix IS
select distinct 
       upper(mmm),mm 
  from SALES_MATRIX_SETHU 
 order 
    by mm;

-- [ Instead of the table as source data you can have your own view according your requirement ]

begin

    v_view_sql_qry := 'create or replace view SALES_matrix_view as 
                       select distinct
                              EMPLOYEE';
                              
    open SALES_matrix;


    loop
        fetch SALES_matrix into v_month, v_mm;
        exit when SALES_matrix%notfound;
        v_view_sql_qry := v_view_sql_qry || ', sum(decode(upper(mmm),''' || v_month ||''',SALES_UNITS,0)) ' || v_month ;

    end loop;                           
    

    v_view_sql_qry := v_view_sql_qry ||' from SALES_MATRIX_SETHU
                                        group 
                                           by EMPLOYEE';
                                          
    insert into ERROR_SQL_LOG values ( v_view_sql_qry);
    commit;                                         

    execute immediate  v_view_sql_qry ;     
   

end;
/


Execute the procedure
BEGIN 
  EARS.SALES_MATRIX_VIEW_CREN;

END; 

Display of View in matrix format

EMPLOYEE	JAN    FEB     MAR

Kalyani	         10	15	25
Kiran	          0	10	 0
Raji	         25	 5	30
Sasi	          0	 0	50
Sethu	         10	20	 0
Shanmugam	  0	 5	 2



If you have any clarification you can mail me. Others if this is not correct way or if there are any other better way can you please guide me.


[EDITED by LF: applied [code] tags to preserve formatting]

[Updated on: Wed, 09 March 2011 00:39] by Moderator

Report message to a moderator

Re: Create Dynamic Matrix View [message #498153 is a reply to message #498150] Tue, 08 March 2011 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Yes there are better way to PIVOT data, they have been posted in every Oracle forum since years.

Regards
Michel
Re: Create Dynamic Matrix View [message #498491 is a reply to message #498153] Thu, 10 March 2011 02:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
10g dynamic pivot:

SCOTT@orcl_11gR2> SELECT * FROM sales_matrix_sethu
  2  /

EMPLOYEE        MMM MM  SALES_UNITS
--------------- --- --- -----------
Sethu           Jan 01           10
Sethu           Feb 02           20
Raji            Jan 01           25
Raji            Feb 02            5
Raji            Mar 03           30
Kiran           Feb 02           10
Sasi            Mar 03           50
Shanmugam       Feb 02            5
Shanmugam       Mar 03            2
Kalyani         Jan 01           10
Kalyani         Feb 02           15
Kalyani         Mar 03           25

12 rows selected.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sales_matrix_view_cren
  2    (v_refcursor OUT SYS_REFCURSOR)
  3  AS
  4    v_sql		VARCHAR2 (32767);
  5  BEGIN
  6    v_sql :=
  7  	 'SELECT employee';
  8    FOR r IN
  9  	 (SELECT DISTINCT mmm
 10  	  FROM	 sales_matrix_sethu
 11  	  ORDER  BY TO_NUMBER (TO_CHAR (TO_DATE (mmm, 'Mon'), 'mm')))
 12    LOOP
 13  	 v_sql := v_sql ||
 14  	   ',NVL (SUM (DECODE (mmm, ''' || r.mmm || ''', sales_units)), 0) ' || r.mmm;
 15    END LOOP;
 16    v_sql := v_sql ||
 17  	 ' FROM   sales_matrix_sethu
 18  	   GROUP  BY empAloyee
 19  	   ORDER  BY employee';
 20    OPEN v_refcursor FOR v_sql;
 21  END sales_matrix_view_cren;
 22  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_11gR2> EXECUTE sales_matrix_view_cren (:g_refcur)

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_refcur

EMPLOYEE               JAN        FEB        MAR
--------------- ---------- ---------- ----------
Kalyani                 10         15         25
Kiran                    0         10          0
Raji                    25          5         30
Sasi                     0          0         50
Sethu                   10         20          0
Shanmugam                0          5          2

6 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Thu, 10 March 2011 03:03]

Report message to a moderator

Re: Create Dynamic Matrix View [message #498623 is a reply to message #498491] Thu, 10 March 2011 10:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL*PLus solution:

SQL> select  *
  2    from  sales_matrix_sethu
  3  /

EMPLOYEE  MMM MM SALES_UNITS
--------- --- -- -----------
Sethu     Jan 01          10
Sethu     Feb 02          20
Raji      Jan 01          25
Raji      Feb 02           5
Raji      Mar 03          30
Kiran     Feb 02          10
Sasi      Mar 03          50
Shanmugam Feb 02           5
Shanmugam Mar 03           2
Kalyani   Jan 01          10
Kalyani   Feb 02          15

EMPLOYEE  MMM MM SALES_UNITS
--------- --- -- -----------
Kalyani   Mar 03          25

12 rows selected.

SQL> column x new_value x noprint format a1000
SQL> select  rtrim(replace(xmlagg(xmlelement(x,mmm,',').extract('//text()') order by mm),'|',''''),',') x
  2    from  (
  3           select  mm,
  4                   'sum(case when mmm = |' || mmm || '| then sales_units end) ' || mmm as mmm
  5             from  sales_matrix_sethu
  6             group by mm,
  7                      mmm
  8          )
  9  /




SQL> set verify off
SQL> select  employee,
  2          &x
  3    from  sales_matrix_sethu
  4    group by employee
  5    order by employee
  6  /

EMPLOYEE         JAN        FEB        MAR
--------- ---------- ---------- ----------
Kalyani           10         15         25
Kiran                        10
Raji              25          5         30
Sasi                                    50
Sethu             10         20
Shanmugam                     5          2

6 rows selected.

SQL> 


SY.
Re: Create Dynamic Matrix View [message #498624 is a reply to message #498623] Thu, 10 March 2011 10:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, could be slightly simplified:

select  *
  from  sales_matrix_sethu
/
column x new_value x noprint
select  replace(xmlagg(xmlelement(x,mmm).extract('//text()') order by mm),'|','''') x
  from  (
         select  mm,
                 ',sum(case when mmm = |' || mmm || '| then sales_units end) ' || mmm as mmm
           from  sales_matrix_sethu
           group by mm,
                    mmm
        )
/
set verify off
select  employee
        &x
  from  sales_matrix_sethu
  group by employee
  order by employee
/


SY.

[Updated on: Thu, 10 March 2011 10:41]

Report message to a moderator

Re: Create Dynamic Matrix View [message #509276 is a reply to message #498624] Thu, 26 May 2011 23:27 Go to previous messageGo to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Assume I have a form in which I would like to display the results of this in a data block how do i do this.

Thank you
Re: Create Dynamic Matrix View [message #509293 is a reply to message #509276] Fri, 27 May 2011 01:23 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd suggest a report instead. It is capable of displaying matrix layout easily, but Forms ... hm, maybe I'm wrong, but you might sweat to do that.
Previous Topic: How to use "select statement" rather than "in clause" in pivot query
Next Topic: ANSI join returning wrong dataset
Goto Forum:
  


Current Time: Thu Mar 28 12:29:19 CDT 2024