Home » SQL & PL/SQL » SQL & PL/SQL » Rowise as column wise sum (Oracle10g)
|
|
| Re: Rowise as column wise sum [message #393124 is a reply to message #393119] |
Fri, 20 March 2009 06:53   |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can start by posting your question in the forum, rather than in an attachement.
OPs actual question:Hi ,
I am creating view at runtime .The select query is as follows it shows following current outpur.
CREATE OR REPLACE VIEW VW_T1 AS
SELECT taskdesc,
(decode(mon, 1, total, 0))Jan ,
(decode(mon, 2, total, 0))Feb,
(decode(mon, 3, total, 0))Mar,
(decode(mon, 4, total, 0))Apr,
(decode(mon, 5, total, 0))May
FROM vw_timesheet_repo;
My current output:
Rn Name Jan FEB Mar Apr May
1 Employeee 0 0 2 0 0
2 Test 0 0 111 0 0
3 IslamicLM 0 0 0 0 20
But i want the output as below.Please help me how can i get this type of output.
Actual Output :
Rn Name Jan FEB Mar Apr May Total
1 Employeee 0 0 2 0 0 2
2 Test 0 0 111 0 0 111
3 IslamicLM 0 0 0 0 20 20
Toal 0 0 113 0 20 133[Updated on: Fri, 20 March 2009 06:54] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Rowise as column wise sum [message #393204 is a reply to message #393119] |
Fri, 20 March 2009 13:39 |
|
|
May be you need code like following,
SELECT DECODE (deptno, NULL, 'Total', deptno), sal_1980, sal_1981, sal_1982,
sal_1987, sal_1980 + sal_1981 + sal_1982 + sal_1987 total
FROM (SELECT deptno,
MAX (DECODE (TO_CHAR (hiredate, 'YYYY'), '1980', sal, 0)
) sal_1980,
MAX (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', sal, 0)
) sal_1981,
MAX (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', sal, 0)
) sal_1982,
MAX (DECODE (TO_CHAR (hiredate, 'YYYY'), '1987', sal, 0)
) sal_1987
FROM emp
GROUP BY ROLLUP (deptno))
Alter the above query to your requirement
|
|
|
|
| Re: Rowise as column wise sum [message #393639 is a reply to message #393119] |
Mon, 23 March 2009 23:27 |
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
|
Member |
|
|
HI thanks for your reply.
But my PM want this query to be developed using SQL not PL/SQL is it possible using SQL.
Sample DDL is given in the attached file.
My PL/SQL code is :
CREATE OR REPLACE PACKAGE BODY demo_pkg AS
PROCEDURE get_query /*p_out OUT types.TYPE)*/
IS
l_in_query LONG := 'select taskdesc ';
v_sql VARCHAR2(1000);
l_out_qry VARCHAR2(1000);
l_add_mon VARCHAR2(100);
lv_max NUMBER(2) := 2;
l_up_qry VARCHAR2(1000);
l_out_q VARCHAR2(1000) := 'SELECT decode(taskdesc, NULL, ''TOTAL''
, taskdesc), ';
BEGIN
SELECT MAX(mon)
INTO lv_max
FROM vw_timesheet_repo;
FOR i IN 1 .. lv_max
LOOP
l_in_query := l_in_query || ', MAX( decode( mon, ' || i ||
', total, 0 )) ' ||
to_char(to_date(i, 'mm'), 'Mon') || '';
l_out_qry := to_char(to_date(i, 'mm'), 'Mon') || ',';
-- dbms_output.put_line('l_out_qry: ' || l_out_qry);
l_add_mon := l_out_qry || l_add_mon ||
to_char(to_date(i, 'mm'), 'Mon') || '+';
END LOOP;
dbms_output.put_line(substr(l_add_mon, 1, (length(l_add_mon) - 1)) ||
' TOTAL FROM ( ');
l_up_qry := substr(l_add_mon, 1, (length(l_add_mon) - 1)) ||
' total FROM ( ';
l_in_query := l_in_query ||
' from vw_timesheet_repo GROUP BY ROLLUP ( taskdesc) ';
dbms_output.put_line(l_out_q || l_up_qry || l_in_query || ' )');
l_main_qry := l_out_q || l_up_qry || l_in_query || ' )';
--
END;
FUNCTION crt_vw
(
p_start DATE,
p_end DATE,
p_out OUT types.TYPE
) RETURN VARCHAR2 IS
e_no_input EXCEPTION;
v_sql VARCHAR2(1000);
out_msg VARCHAR2(100);
BEGIN
IF p_start IS NULL OR
p_end IS NULL THEN
RAISE e_no_input;
END IF;
v_sql := ' CREATE OR REPLACE view vw_timesheet_repo AS
SELECT taskdesc,
to_char(tm.actual_end_date, ''MM'') AS mon,
SUM(tm.manhrs) total
FROM task_master tm
WHERE to_char(tm.actual_start_date) >= to_char(' || '''' ||
p_start || '''' ||
')AND to_char(tm.actual_start_date) <= to_char(' || '''' ||
p_end || '''' || ')
AND tm.status <> ''X''
GROUP BY to_char(tm.actual_end_date, ''MM''),
taskdesc
ORDER BY to_char(tm.actual_end_date, ''MM''),
taskdesc';
-- EXECUTE IMMEDIATE v_sql;
dbms_output.put_line(v_sql);
-- dbms_output.put_line('View Is Created Successfuly');
get_query;
OPEN p_out FOR l_main_qry;
out_msg := 'Sucess';
RETURN(out_msg);
EXCEPTION
WHEN e_no_input THEN
out_msg := 'Error!..Please Provide All The Inputs.';
RETURN(out_msg);
END;
END;
-
Attachment: tm_sql.sql
(Size: 3.32KB, Downloaded 685 times)
|
|
|
|
|
|
|
|
|
|
| Re: Rowise as column wise sum [message #393693 is a reply to message #393648] |
Tue, 24 March 2009 01:50 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| sr_orcl wrote on Tue, 24 March 2009 06:19 | Hi,
He want this query should be developed using Model clause.Can oyu please suggest me some idea..i m new to model clause.
|
Is this an exercise for you or plain work?
If it is plain work, ask your project manager why he thinks he needs to prescribe you how to do your work
If it is an exercise, well, I guess the idea is that YOU figure out how to do it.
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 16:31:44 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
