Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PIVOT in Oracle

sanJun 29 2018 — edited Jul 10 2018

Hi All,

I am trying to create a pivot query using the below test data

My table looks like

City          Group  agent  paymentdate actno

Chennai   South  X1       05/01/2018 .1

Bangalore south D1       05/02/2018.  2

Delhi.         North C1.      05/02/2018.  3

Chennai.   South. B1.     05/01/2018.  4

Chennai .  South. X1 .    05/01/2018.   5

I am trying to get the output as below

City Group agent 05/01/2018 05/02/2018 05/03/2018

Chennai south X1   2.                    0.                 0

Bangalore south D1 0.                  1.                  0

Delhi.    North .  C1.  0.                  1                   0

Chennai South . B1.  1 .                 0 .                0

Note : Date will dynamic for the month. I have to display data for the month

I tired with PiVot but I have to hardcode the value in for In clause.

How to pass it as dynamic? Or is there any other way to achieve this?

Thanks in advance.

Cheers

San
This post has been answered by Etbin on Jun 29 2018
Jump to Answer

Comments

John Thorton 
san wrote:
Hi All,
I am trying to create a pivot query using the below test data
My table looks like 
City Group agent paymentdate actno
Chennai South X1 05/01/2018 .1
Bangalore south D1 05/02/2018. 2
Delhi. North C1. 05/02/2018. 3
Chennai. South. B1. 05/01/2018. 4
Chennai . South. X1 . 05/01/2018. 5
I am trying to get the output as below
City Group agent 05/01/2018 05/02/2018 05/03/2018
Chennai south X1 2. 0. 0
Bangalore south D1 0. 1. 0
Delhi. North . C1. 0. 1 0
Chennai South . B1. 1 . 0 . 0
Note : Date will dynamic for the month. I have to display data for the month
I tired with PiVot but I have to hardcode the value in for In clause.
How to pass it as dynamic? Or is there any other way to achieve this?
Thanks in advance.
Cheers
San

How do I ask a question on the forums?

MONTH?

Calendar month?

Month before, after or during/

How many days does your month have?

Please clarify.

san

Yes it's calendar month. If I pass the parameter 'May' I should display for everyday in the month of May.

05/01/2018 ....... 05/31/2018

Etbin
Answer 
Or is there any other way to achieve this?

You might need a query that will write your query

select 'select * from (select city,group_,agent,paymentdate from data) '||

       'pivot (count(*) for paymentdate in ('||

       listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query

  from (select to_date(month_year,'mm_yyyy') + level - 1 dte

          from (select :mm_yyyy month_year

                  from dual

               )

        connect by level <= to_number(to_char(last_day(to_date(month_year,'mm_yyyy')),'dd'))

       )

with

data as

(select 'Chennai' city,'South' group_,'X1' agent,to_date('05/01/2018','mm/dd/yyyy') paymentdate,1 actno from dual union all

select 'Bangalore','south','D1',to_date('05/02/2018','mm/dd/yyyy'),2 from dual union all

select 'Delhi','North','C1',to_date('05/02/2018','mm/dd/yyyy'),3 from dual union all

select 'Chennai','South','B1',to_date('05/01/2018','mm/dd/yyyy'),4 from dual union all

select 'Chennai','South','X1',to_date('05/01/2018','mm/dd/yyyy'),5 from dual union all

select 'Bangalore','south','D1',to_date('06/02/2018','mm/dd/yyyy'),2 from dual union all

select 'Delhi','North','C1',to_date('06/03/2018','mm/dd/yyyy'),3 from dual union all

select 'Chennai','South','B1',to_date('06/01/2018','mm/dd/yyyy'),4 from dual

)

select * from (select city,group_,agent,paymentdate from data) pivot (count(*) for paymentdate in (date '2018-05-01' "05/01/2018",date '2018-05-02' "05/02/2018",date '2018-05-03' "05/03/2018",date '2018-05-04' "05/04/2018",date '2018-05-05' "05/05/2018",date '2018-05-06' "05/06/2018",date '2018-05-07' "05/07/2018",date '2018-05-08' "05/08/2018",date '2018-05-09' "05/09/2018",date '2018-05-10' "05/10/2018",date '2018-05-11' "05/11/2018",date '2018-05-12' "05/12/2018",date '2018-05-13' "05/13/2018",date '2018-05-14' "05/14/2018",date '2018-05-15' "05/15/2018",date '2018-05-16' "05/16/2018",date '2018-05-17' "05/17/2018",date '2018-05-18' "05/18/2018",date '2018-05-19' "05/19/2018",date '2018-05-20' "05/20/2018",date '2018-05-21' "05/21/2018",date '2018-05-22' "05/22/2018",date '2018-05-23' "05/23/2018",date '2018-05-24' "05/24/2018",date '2018-05-25' "05/25/2018",date '2018-05-26' "05/26/2018",date '2018-05-27' "05/27/2018",date '2018-05-28' "05/28/2018",date '2018-05-29' "05/29/2018",date '2018-05-30' "05/30/2018",date '2018-05-31' "05/31/2018"))

CITYGROUP_AGENT05/01/201805/02/201805/03/201805/04/201805/05/201805/06/201805/07/201805/08/201805/09/201805/10/201805/11/201805/12/201805/13/201805/14/201805/15/201805/16/201805/17/201805/18/201805/19/201805/20/201805/21/201805/22/201805/23/201805/24/201805/25/201805/26/201805/27/201805/28/201805/29/201805/30/201805/31/2018
DelhiNorthC10100000000000000000000000000000
ChennaiSouthB11000000000000000000000000000000
ChennaiSouthX12000000000000000000000000000000
BangaloresouthD1Marked as Answer by san · Sep 27 2020
san

Thanks Etbin for your time. You mean I need to hardcode the date values in the above mentioned query? Actually user can pass any month as a input. I cannot hardcode the date values.

Etbin

You said you want it dynamic.

  • The user passes any month (most probably the year is needed too)
  • you submit the month received from the user to the query writer (the first query)
  • the query writes the query you need
  • you execute the query (written by the first query)  and return the result to the user

read https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011

Regards

Etbin

san

Etbin

You mean the below query will give my output?

select 'select * from (select city,group_,agent,paymentdate from data) '||

       'pivot (count(*) for paymentdate in ('||

       listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query

  from (select to_date(month_year,'mm_yyyy') + level - 1 dte

          from (select :mm_yyyy month_year

                  from dual

               )

        connect by level <= to_number(to_char(last_day(to_date(month_year,'mm_yyyy')),'dd'))

       )

san

Hi Etbin,

Thanks for your help , the query returns the required query. I can see the output. Now I am trying to make the whole query as dynamic , so that I can return the sql to my function. the function will execute the query and return the output.

Is that possible approach?

Etbin

Take a look at

Example 7-4 Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements

in the link provided

you need to replace the line under the comment -- Dynamic SQL statement with placeholder: with something as

select 'select * from (select city,group_,agent,paymentdate from data) '||

       'pivot (count(*) for paymentdate in ('||

       listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query

into cursor_text  -- make it varchar2(4000) as varchar2(200) won't be big enough

  from (select to_date(month_year,'mm_yyyy') + level - 1 dte

          from (select :mm_yyyy month_year

                  from dual

               )

        connect by level <= to_number(to_char(last_day(to_date(month_year,'mm_yyyy')),'dd'))

       )

then open the cursor for cursor_text (no using clause needed)

then fetch from the cursor

...

Regards

Etbin

san

Hi ,

I am trying to create a function using the above query , thought it will return the sql to me, I can use that sql in my application.

But when I tried to create a function

declare

v_sql varchar2(4000);

begin

select query into v_sql from (

select 'select * from (select city,group_,agent,paymentdate from data) '||

       'pivot (count(*) for paymentdate in ('||

       listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query

  from (select to_date(month_year,'mm_yyyy') + level - 1 dte

          from (select :mm_yyyy month_year

                  from dual

               )

        connect by level <= to_number(to_char(last_day(to_date(month_year,'mm_yyyy')),'dd'))

       )

) dual;

return v_sql;

end;

Not able to create a function , but with the same query I can able to create a procedure. why this different?

Error message which I received is

Error at line 8: PLS-00103: Encountered the symbol "GROUP" when expecting one of the following: , from

_Dylan_

Compiles and runs fine for me...

create or replace function f1 (p_mm_yyyy in varchar2)
return varchar2
as
  v_sql varchar2(4000);
begin
  select query into v_sql from (
    select 'select * from (select city,group_,agent,paymentdate from data) '||
           'pivot (count(*) for paymentdate in ('||
           listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query
      from (select to_date(month_year,'mm_yyyy') + level - 1 dte
              from (select p_mm_yyyy month_year
                      from dual
                   )
            connect by level <= to_number(to_char(last_day(to_date(month_year,'mm_yyyy')),'dd'))
           )
  ) dual;
  
return v_sql;
end;
/
select f1('08_1979') from dual;
san

Hi Dylan,

Thanks for the help. It's solved my existing issue. I need to use this query and devloep a report in Oracle apex. In Apex I am having some issue. I believe that's kind of internal issue with apex.

I am thinking about the alternative way.

1) using the above query is that possible to create a view or MView ? Mean I need to add one more column as Month value should be to_char(paymentdate,'MON-YYYY') . My view column should be

City Group Agent Month Col1 col2 col3..... Col31

Col 1 to col 31 should be our date of the month.  For example

Chennai South X May col 1 col2 ( here Col1 & 2 is May 1 and May 2)

Once this view is populated . I need to bring down the output like below

First city wise count of payment date next city and group count and next city group Agent kind of drill down report.

Group by with city first then city and group and city group and agent.

Thanks in advance.

Cheers,

San

Etbin

Just playing around. (adding a row of column names - (when using " didn't work yet) suggested by Frank several times )

select * from san_data

CITYGROUP_AGENTPAYMENTDATEACTNO
ChennaiSouthX101-MAY-2018 00:00:001
BangaloresouthD102-MAY-2018 00:00:002
DelhiNorthC102-MAY-2018 00:00:003
ChennaiSouthB101-MAY-2018 00:00:004
ChennaiSouthX101-MAY-2018 00:00:005
BangaloresouthD102-JUN-2018 00:00:002
DelhiNorthC103-JUN-2018 00:00:003
ChennaiSouthB101-JUN-2018 00:00:004

create or replace function san_function(p_month_year in varchar2) return table_varchar2 pipelined is

  l_theCursor     integer default dbms_sql.open_cursor;

  l_query         varchar2(4000);

  l_columnValue   varchar2(4000);

  l_status        integer;

  l_colCnt        number default 0;

  l_separator     varchar2(1) := '|';

  l_month_year    varchar2(7) := lpad(p_month_year,7,'0');

  l_descTbl       dbms_sql.desc_tab;

  l_csv           table_varchar2 := table_varchar2();

begin

  select 'select * from (select city,group_,agent,paymentdate from san_data where to_char(paymentdate,''mm_yyyy'') = '''||

          l_month_year || ''') '||

         'pivot (count(*) for paymentdate in ('||

         listagg('date '''||to_char(dte,'yyyy-mm-dd')||''' "'||to_char(dte,'mm/dd/yyyy')||'"',',') within group (order by dte)||'))' query

    into l_query

    from (select to_date(l_month_year,'mm_yyyy') + level - 1 dte

            from dual

          connect by level <= to_number(to_char(last_day(to_date(l_month_year,'mm_yyyy')),'dd'))

         );

--  dbms_output.put_line(l_query);  -- for debugging

  dbms_sql.parse(l_theCursor,l_query,dbms_sql.native);

 

  dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);

  l_csv.extend(1);

  for i in 1 .. l_colCnt

  loop

    l_csv(1) := l_csv(1) || l_descTbl(i).col_name || l_separator;

    dbms_sql.define_column(l_theCursor,i,l_columnValue,4000);

  end loop;

  pipe row(l_csv(1));

  l_status := dbms_sql.execute(l_theCursor);

  while (dbms_sql.fetch_rows(l_theCursor) > 0)

  loop

    l_csv(1) := '';

    for i in 1 .. l_colCnt

    loop

      dbms_sql.column_value(l_theCursor,i,l_columnValue);

      l_csv(1) := l_csv(1) || l_columnValue || l_separator;

    end loop;

    pipe row(l_csv(1));

  end loop;

  dbms_sql.close_cursor(l_theCursor);

         

end;

Now:

with

slicer(val,rest,rw,cl) as

(select substr(column_value,1,instr(column_value,'|') - 1),substr(column_value,instr(column_value,'|') + 1),row_number() over (order by null),1

   from table(san_function('5_2018'))

union all

select substr(rest,1,instr(rest,'|') - 1),substr(rest,instr(rest,'|') + 1),rw,cl + 1

   from slicer

  where rest is not null

)

select city,group_,agent,day_1,day_2,day_3,day_4,day_5,day_6,day_7,day_8,day_9,day_10,

       day_11,day_12,day_13,day_14,day_15,day_16,day_17,day_18,day_19,day_20,

       day_21,day_22,day_23,day_24,day_25,day_26,day_27,day_28,day_29,day_30,day_31

  from (select rw,cl,val

          from slicer

       )

pivot (max(val) for cl in (1 city,2 group_,3 agent,4 day_1,5 day_2,6 day_3,7 day_4,8 day_5,9 day_6,10 day_7,11 day_8,12 day_9,13 day_10,

                            14 day_11,15 day_12,16 day_13,17 day_14,18 day_15,19 day_16,20 day_17,21 day_18,22 day_19,23 day_20,

                            24 day_21,25 day_22,26 day_23,27 day_24,28 day_25,29 day_26,30 day_27,31 day_28,32 day_29,33 day_30,34 day_31

                           )

       )

order by rw

CITYGROUP_AGENTDAY_1DAY_2DAY_3DAY_4DAY_5DAY_6DAY_7DAY_8DAY_9DAY_10DAY_11DAY_12DAY_13DAY_14DAY_15DAY_16DAY_17DAY_18DAY_19DAY_20DAY_21DAY_22DAY_23DAY_24DAY_25DAY_26DAY_27DAY_28DAY_29DAY_30DAY_31
CITYGROUP_AGENT05/01/201805/02/201805/03/201805/04/201805/05/201805/06/201805/07/201805/08/201805/09/201805/10/201805/11/201805/12/201805/13/201805/14/201805/15/201805/16/201805/17/201805/18/201805/19/201805/20/201805/21/201805/22/201805/23/201805/24/201805/25/201805/26/201805/27/201805/28/2018
1 person found this helpful
san

Etbin,

I apologies for not mentioning earlier, here i restricted to use proc or func, All i can do is create view and do pivot. I have done below part as of now

using below query i have a created a table (Note: Added one more column)

SELECT * FROM (SELECT city,branch,group,agent,TO_CHAR(payment_date,'DD') MONTH,payment_date

FROM agent_details

WHERE payment_date BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') AND LAST_DAY(ADD_MONTHS(SYSDATE,-2)))

PIVOT (COUNT(*)

FOR MONTH IN (01,02,03,04,05,06,07,08,09,10,

11,12,13,14,15,16,17,18,19,20,21,22,23,

24,25,26,27,28,29,30,31))order by payment_date;

i will run a job and merge the table every month. my table looks like below

City          Branch Group  agent  paymentdate  01 02 03 04 05 06 07 ............. 31

Chennai          x        South  X1       05/01/2018  1  0    0   0 0  0   0

Bangalore       y        south D1        05/02/2018.  0    1 0 0 0 0 0 

Delhi.              P         North C1.      05/02/2018.  0    1 0 0 0 0 0

Chennai.        z          west. B1.     05/01/2018.  1 0 0 0 0 0 0 0 0

Chennai .       x           South. X1 .    05/01/2018.   1 0 0 0 0 0 0 0

Need a output like below

City          Branch Group  agent  01 02 03 04 05 06 07 ............. 31  total

Chennai   -           -             -        5  2  1  0 4  1  5   5 ................ 6     125      ( Group wise CITY alone)

Chennai  x        south     x1         2  1  1  0 1   0  2  2 ................. 2     55       (group wise city , branch)

                          west      b1         1  0  0  0  2  0   1 1 ................. 1     40

               z         West   a1            1  0  0 0 1 0 0 1..............  2            30

                          North  b1           1  0  0 0  0 0 0 0  ............ 1              2

total    - -  - - -                               10 25 30 45 50 60 ----------- 100      680    

like all city breakup and total needs to add.

Cheers,

San

Etbin

Something to play with

with

data as

(select city,branch,grp,

        case when city = 'Bangalore' then 'W'||to_char(trunc(dbms_random.value(1,6)))

             when city = 'Chennai' then 'X'||to_char(trunc(dbms_random.value(1,5)))

             when city = 'Delhi' then 'Y'||to_char(trunc(dbms_random.value(1,10)))

        end agent,

        extract(day from paymentdate) day

   from (select case trunc(dbms_random.value(1,4)) when 1 then 'Bangalore' when 2 then 'Chennai' when 3 then 'Delhi' end city,

                case trunc(abs(dbms_random.normal)) when 1 then 'B1' when 2 then 'B2' when 3 then 'B3' else 'B4' end branch,

                case trunc(dbms_random.value(1,5)) when 1 then 'North' when 2 then 'South' when 3 then 'East' when 4 then 'West' end grp,

                trunc(sysdate,'mm') + dbms_random.value(0,31) paymentdate

           from dual

         connect by level < 100000

        )

),

sum_cols as

(select city,branch,grp,agent,

"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31",

"1"+"2"+"3"+"4"+"5"+"6"+"7"+"8"+"9"+"10"+"11"+"12"+"13"+"14"+"15"+"16"+"17"+"18"+"19"+"20"+"21"+"22"+"23"+"24"+"25"+"26"+"27"+"28"+"29"+"30"+"31" total

   from (select city,branch,grp,agent,day

           from data

          where city = 'Delhi'

            and branch in ('B1','B3')

            and grp = 'East'

            and agent between 'Y7' and 'Y9'

        )

pivot (count(*) for day in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

)

select *

  from sum_cols

union all

select null,null,null,null,sum("1") "1",sum("2") "2",sum("3") "3",sum("4") "4",sum("5") "5",sum("6") "6",sum("7") "7",sum("8") "8",sum("9") "9",sum("10") "10",sum("11") "11",sum("12") "12",sum("13") "13",sum("14") "14",sum("15") "15",sum("16") "16",sum("17") "17",sum("18") "18",sum("19") "19",sum("20") "20",sum("21") "21",sum("22") "22",sum("23") "23",sum("24") "24",sum("25") "25",sum("26") "26",sum("27") "27",sum("28") "28",sum("29") "29",sum("30") "30",sum("31") "31",sum(total)

  from sum_cols

CITYBRANCHGRPAGENT12345678910111213141516171819202122232425262728293031TOTAL
DelhiB1EastY710913105797121155113658129977621063107115240
DelhiB1EastY889910715614101269946591271171211910101195135280
DelhiB1EastY97121497341066149710127456713107885777
san

Hi Etbin,

I have done the below part , i have loaded a table using pivot query , using the table data i am trying the build the below query

WITH WITH_CITYREPTBL AS

(

SELECT "City" SEQ, "City" ,"Branch " ,"Group","Agent","COL1","COL2","COL3","COL4","COL5",

"COL6","COL7","COL8","COL9","COL10","COL11","COL12","COL13","COL14","COL15","COL16","COL17","COL18","COL19","COL20",

"COL21","COL22","COL23","COL24","COL25","COL26","COL27","COL28","COL29","COL30","COL31",

("COL1"+"COL2"+"COL3"+"COL4"+"COL5"+"COL6"+"COL7"+"COL8"+"COL9"+"COL10"+

"COL11"+"COL12"+"COL13"+"COL14"+"COL15"+"COL16"+"COL17"+"COL18"+"COL19"+"COL20"+"COL21"+"COL22"+

"COL23"+"COL24"+"COL25"+"COL26"+"COL27"+"COL28"+"COL29"+"COL30"+"COL31") as Total

  FROM

City_rep_tbl where REPORT_DT=:P2_PERIOD

) 

,

WITH_CITYREPTBL2 AS

(SELECT  "City"||' Total' SEQ,

'<div style="cursor:pointer" class="parent-row" onclick="javascript:open_row(this)">+  <b>'|| "City" ||' Total </b> </div>' "City",

null as "Branch " ,NULL as "Group",NULL,

SUM("COL1") "01",SUM("COL2") "02",SUM("COL3") "03",SUM("COL4") "04",SUM("COL5") "05",SUM("COL6") "06",SUM("COL7") "07",SUM("COL8") "08",SUM("COL9") "09",

SUM("COL10") "10",SUM("COL11")"11",SUM("COL12") "12",SUM("COL13") "13",SUM("COL14") "14",SUM("COL15") "15",SUM("COL16") "16",

SUM("COL17") "17",SUM("COL18") "18",SUM("COL19") "19",SUM("COL20") "20",SUM("COL21") "21",SUM("COL22") "22",SUM("COL23") "23",SUM("COL24") "24",

SUM("COL25") "25",SUM("COL26")"26",SUM("COL27") "27",SUM("COL28") "28",SUM("COL29") "29",SUM("COL30") "30",SUM("COL31") "31",

sum(Total) TOTAL FROM WITH_CITYREPTBL group by "City"

union

SELECT   "City"||' Total' SEQ, null as "City" ,

"Branch " AS "Branch ",

null as "Group",NULL,SUM("COL1") "01",SUM("COL2") "02",SUM("COL3") "03",SUM("COL4") "04",SUM("COL5") "05",SUM("COL6") "06",SUM("COL7") "07",SUM("COL8") "08",SUM("COL9") "09",

SUM("COL10") "10",SUM("COL11")"11",SUM("COL12") "12",SUM("COL13") "13",SUM("COL14") "14",SUM("COL15") "15",SUM("COL16") "16",

SUM("COL17") "17",SUM("COL18") "18",SUM("COL19") "19",SUM("COL20") "20",SUM("COL21") "21",SUM("COL22") "22",SUM("COL23") "23",SUM("COL24") "24",

SUM("COL25") "25",SUM("COL26")"26",SUM("COL27") "27",SUM("COL28") "28",SUM("COL29") "29",SUM("COL30") "30",SUM("COL31") "31",

sum(Total) TOTAL FROM WITH_CITYREPTBL group by "City","Branch "

union

SELECT   "City"||' Total' SEQ, null as "City" ,

"Branch " as "Branch " ,

"Group" as "Group",NULL,SUM("COL1") "01",SUM("COL2") "02",SUM("COL3") "03",SUM("COL4") "04",SUM("COL5") "05",SUM("COL6") "06",SUM("COL7") "07",SUM("COL8") "08",SUM("COL9") "09",

SUM("COL10") "10",SUM("COL11")"11",SUM("COL12") "12",SUM("COL13") "13",SUM("COL14") "14",SUM("COL15") "15",SUM("COL16") "16",

SUM("COL17") "17",SUM("COL18") "18",SUM("COL19") "19",SUM("COL20") "20",SUM("COL21") "21",SUM("COL22") "22",SUM("COL23") "23",SUM("COL24") "24",

SUM("COL25") "25",SUM("COL26")"26",SUM("COL27") "27",SUM("COL28") "28",SUM("COL29") "29",SUM("COL30") "30",SUM("COL31") "31",

sum(Total) TOTAL FROM WITH_CITYREPTBL group by "City","Branch ","Group"

)

,WITH_CITYREPTBL3 AS

(SELECT '1Total' SEQ,'<div  class="parent-row"><b> Total </b> </div>' as "City", null as "Branch " , NULL as "Group",NULL,

SUM("COL1") "01",SUM("COL2") "02",SUM("COL3") "03",SUM("COL4") "04",SUM("COL5") "05",SUM("COL6") "06",SUM("COL7") "07",SUM("COL8") "08",SUM("COL9") "09",

SUM("COL10") "10",SUM("COL11")"11",SUM("COL12") "12",SUM("COL13") "13",SUM("COL14") "14",SUM("COL15") "15",SUM("COL16") "16",

SUM("COL17") "17",SUM("COL18") "18",SUM("COL19") "19",SUM("COL20") "20",SUM("COL21") "21",SUM("COL22") "22",SUM("COL23") "23",SUM("COL24") "24",

SUM("COL25") "25",SUM("COL26")"26",SUM("COL27") "27",SUM("COL28") "28",SUM("COL29") "29",SUM("COL30") "30",SUM("COL31") "31",

sum(Total) FROM WITH_CITYREPTBL

),

WITH_CITYREPTBL AS

(SELECT * FROM WITH_CITYREPTBL2

UNION ALL

SELECT * FROM WITH_CITYREPTBL3

)

select * from WITH_CITYREPTBL4

Actual Output:

Chennai Total <div style="cursor:pointer" class="parent-row" onclick="javascript:open_row(this)">+;;<b>Chennai Total </b> </div> 414 340 543 970 287 27 350 409 443 437 1023 238 84 383 619 486 696 1326 412 82 437 582 474 532 1278 462 32 253 506 602 2803 17530

Chennai Total X South 0 0 0 1 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 3 7

Chennai Total X North 4 6 5 17 8 0 6 10 9 6 22 6 3 5 11 6 14 27 3 0 7 13 5 10 21 0 0 8 2 6 26 266

Chennai Total X West 1 2 4 5 0 0 7 2 4 7 11 3 0 6 6 3 5 7 2 1 3 4 4 7 7 5 0 3 2 3 13 127

Chennai Total X East 63 65 62 130 30 9 70 54 57 52 65 23 7 43 50 52 50 77 26 7 40 41 30 28 84 37 3 20 45 37 70 1427

Chennai Total X     68 73 71 153 39 9 83 66 70 65 98 32 11 54 67 61 69 111 31 8 50 58 39 45 113 42 3 31 49 46 112 1827

First Row - I am displaying Over all Day wise count for Chennai

2,3,4,5 row - Actual result it dispaying City branch gorup day wise count for Chennai 

Last row i am displaying Day wise count of City and branch

Expected Result

First row - Need to display Over all day wise count for Chennai

Second row - Need to display day wise count for City and branch

Finally I need to display city branch and group day wise count.

These needs to done for all city branch and group . (Also i need to add the same <div style="cursor:pointer" class="parent-row" part to branch column in second union query )

the above query is almost working , only thing the last row needs to come as second row , also if i add div style branch column in second union the order is changing.

Etbin

Maybe

with

data as

(select city,branch,grp,

        case when city = 'Bangalore' then 'W'||to_char(trunc(dbms_random.value(1,6)))

             when city = 'Chennai' then 'X'||to_char(trunc(dbms_random.value(1,5)))

             when city = 'Delhi' then 'Y'||to_char(trunc(dbms_random.value(1,10)))

        end agent,

        extract(day from paymentdate) day

   from (select case trunc(dbms_random.value(1,4)) when 1 then 'Bangalore' when 2 then 'Chennai' when 3 then 'Delhi' end city,

                case trunc(abs(dbms_random.normal)) when 1 then 'B1' when 2 then 'B2' when 3 then 'B3' else 'B4' end branch,

                case trunc(dbms_random.value(1,5)) when 1 then 'North' when 2 then 'South' when 3 then 'East' when 4 then 'West' end grp,

                trunc(sysdate,'mm') + dbms_random.value(0,31) paymentdate

           from dual

         connect by level < 100000

        )

),

sum_cols as

(select city,branch,grp,agent,

"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31",

"1"+"2"+"3"+"4"+"5"+"6"+"7"+"8"+"9"+"10"+"11"+"12"+"13"+"14"+"15"+"16"+"17"+"18"+"19"+"20"+"21"+"22"+"23"+"24"+"25"+"26"+"27"+"28"+"29"+"30"+"31" total

   from (select city,branch,grp,agent,day

           from data

          where city = 'Chennai'

--          and ...    additional filtering if required

        )

pivot (count(*) for day in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))

)

select *

  from (select city,branch,grp/*,agent*/,sum("1") "1",sum("2") "2",sum("3") "3",sum("4") "4",sum("5") "5",sum("6") "6",sum("7") "7",sum("8") "8",sum("9") "9",sum("10") "10",sum("11") "11",sum("12") "12",sum("13") "13",sum("14") "14",sum("15") "15",sum("16") "16",sum("17") "17",sum("18") "18",sum("19") "19",sum("20") "20",sum("21") "21",sum("22") "22",sum("23") "23",sum("24") "24",sum("25") "25",sum("26") "26",sum("27") "27",sum("28") "28",sum("29") "29",sum("30") "30",sum("31") "31",sum(total)

          from sum_cols

         group by rollup(city,branch,grp/*,agent*/)

       )

where city is not null

order by city,branch nulls first,decode(grp,'North',1,'South',2,'East',3,'West',4) nulls first

CITYBRANCHGRP12345678910111213141516171819202122232425262728293031SUM(TOTAL)
Chennai--107111151033103210321027112510931091101810831090106910881040108010761083103110821031109010651073110010841080105510931121106133212
ChennaiB1-2913382742652822832992733152733033272812972712982923212712932842952993173113032983223053253279233
ChennaiB1North688470647068766477727572537668766281646980667992918673807076722274
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 7 2018
Added on Jun 29 2018
16 comments
889 views