Home » SQL & PL/SQL » SQL & PL/SQL » Rowise as column wise sum (Oracle10g)
Rowise as column wise sum [message #393119] Fri, 20 March 2009 06:36 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
Pleas tell me how can i get the this type of ouput
  • Attachment: forum.sql
    (Size: 0.79KB, Downloaded 156 times)
Re: Rowise as column wise sum [message #393124 is a reply to message #393119] Fri, 20 March 2009 06:53 Go to previous messageGo to next message
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 #393125 is a reply to message #393119] Fri, 20 March 2009 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some of us can't or don't want to download file.
As it is less than 800 characters, post it here.

Before, 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Don't forget to post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Rowise as column wise sum [message #393134 is a reply to message #393125] Fri, 20 March 2009 08:08 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Please suggest me soem idea
Re: Rowise as column wise sum [message #393138 is a reply to message #393134] Fri, 20 March 2009 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sr_orcl wrote on Fri, 20 March 2009 14:08
Please suggest me soem idea

SUM

Regards
Michel

Re: Rowise as column wise sum [message #393204 is a reply to message #393119] Fri, 20 March 2009 13:39 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous messageGo to next message
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 128 times)
Re: Rowise as column wise sum [message #393640 is a reply to message #393119] Mon, 23 March 2009 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>But my PM want this query to be developed using SQL not PL/SQL is it possible using SQL.
Ask your PM how he or you plan on resolving YOUR issue.
We are not required to meet your PM's self imposed limitations.

Nothing is impossible for the person who does not have to do it!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 23 March 2009 23:37]

Report message to a moderator

Re: Rowise as column wise sum [message #393648 is a reply to message #393119] Tue, 24 March 2009 00:19 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,

He want this query should be developed using Model clause.Can oyu please suggest me some idea..i m new to model clause.
Re: Rowise as column wise sum [message #393657 is a reply to message #393119] Tue, 24 March 2009 00:27 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>He want this query should be developed using Model clause.
Then have PM provide solution.
Re: Rowise as column wise sum [message #393693 is a reply to message #393648] Tue, 24 March 2009 01:50 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: advantages of 10g
Next Topic: Tuning the query
Goto Forum:
  


Current Time: Mon Dec 05 23:43:47 CST 2016

Total time taken to generate the page: 0.08953 seconds