Home » SQL & PL/SQL » SQL & PL/SQL » Create Dynamic Matrix View
| Create Dynamic Matrix View [message #498150] |
Tue, 08 March 2011 23:20  |
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 #498491 is a reply to message #498153] |
Thu, 10 March 2011 02:58  |
 |
Barbara Boehmer
Messages: 9102
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 |
Solomon Yakobson
Messages: 3303
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 |
Solomon Yakobson
Messages: 3303
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 #509293 is a reply to message #509276] |
Fri, 27 May 2011 01:23 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
Goto Forum:
Current Time: Mon Jan 20 07:33:58 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
