Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

Pivot Query help

I believe its called a Pivot Query, but I may be wrong...

I have a query that gives me these results
THD_CUSTNO	TH_PRODNO	SDATE		LAST30
7185		96		26-Oct-12	6
7185		174		5-Oct-12	12
7185		175		22-Oct-12	12
7185		177		24-Oct-12	12
7185		203		1-Oct-12	9
7185		219		1-Oct-12	8
7185		219		19-Oct-12	16
7185		219		26-Oct-12	8
7185		403		22-Oct-12	8
7185		410		3-Oct-12	6
7185		410		22-Oct-12	6

Open in new window



I am looking for a way to get that same data, in this format here
THD_CUSTNO	TH_PRODNO	1-Oct-12	2-Oct-12	3-Oct-12	4-Oct-12	5-Oct-12	6-Oct-12	7-Oct-12	8-Oct-12	9-Oct-12	10-Oct-12	11-Oct-12	12-Oct-12	13-Oct-12	14-Oct-12	15-Oct-12	16-Oct-12	17-Oct-12	18-Oct-12	19-Oct-12	20-Oct-12	21-Oct-12	22-Oct-12	23-Oct-12	24-Oct-12	25-Oct-12	26-Oct-12	27-Oct-12	28-Oct-12	29-Oct-12	30-Oct-12	31-Oct-12
7185		96																																																				6					
7185		174										12																										
7185		175																																												12									
7185		177																																																12							
7185		203		9																														
7185		219		8																														16																				8					
7185		403																																												8									
7185		410						6																																						6									

Open in new window




This is the query I have for the first set of results
SELECT
  thd_custno,
  th_prodno,
  sdate,
  round((sum(th_units)),2) LAST30
FROM
                                          (select 
                                              thd_Custno,
                                              th_prodno,
                                              th_units,
                                              TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YY') AS sdate
                                          from thsdtl@"DB2")
                                          WHERE thd_custno = '7185' AND SDATE BETWEEN (select trunc(trunc(sysdate,'MM')-1,'MM') from dual) and (select trunc(sysdate,'MM')-1 from dual)
                                          group by 
                                          thd_custno, th_prodno, sdate

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of futureDBA
futureDBA

ASKER

There is no way this can be done? without hardcoding it manually ?
Avatar of Walter Ritzel
Is there a way, but then you need to use some XML parsing.
I have created a table with the result of your query, just to simplify:

drop table thsdt1 purge;
create table thsdt1 (custno integer, prodno integer, sdate date, last30 integer);
insert into thsdt1 values(7185,	96,	'26-Oct-12',	6);
insert into thsdt1 values(7185,	174,	'5-Oct-12',	12);
insert into thsdt1 values(7185,	175,	'22-Oct-12',	12);
insert into thsdt1 values(7185,	177,	'24-Oct-12',	12);
insert into thsdt1 values(7185,	203,	'1-Oct-12',	9);
insert into thsdt1 values(7185,	219,	'1-Oct-12',	8);
insert into thsdt1 values(7185,	219,	'19-Oct-12',	16);
insert into thsdt1 values(7185,	219,	'26-Oct-12',	8);
insert into thsdt1 values(7185,	403,	'22-Oct-12',	8);
insert into thsdt1 values(7185,	410,	'3-Oct-12',	6);
insert into thsdt1 values(7185,	410,	'22-Oct-12',	6);
commit;

select * from (
SELECT custno,
  prodno,
  sdate
  , last30
FROM thsdt1
WHERE custno = '7185'
AND SDATE BETWEEN (SELECT TRUNC(TRUNC(sysdate,'MM')-1,'MM') FROM dual) AND (SELECT TRUNC(sysdate,'MM')-1 FROM dual)
) PIVOT XML (sum(last30) for sdate in (ANY));

Open in new window

Using PL/SQL you can create a function that returns a ref cursor (not much help) or dynamically build the SQL that you want to execute, spool it and then execute it:


declare

  l_month_day_list varchar2(32767);
  l_sql            varchar2(32767);
  l_ref            sys_refcursor;

begin

  select listagg(col_header,',') within group(order by null)
  into l_month_day_list
  from
  (
  SELECT
  'DATE'''||TO_CHAR(month_day,'RRRR-MM-DD')||''' AS "'||TO_CHAR(month_day,'DD-MON-RR')||'"' col_header
  FROM
  (
  select start_date + rownum - 1 month_day
  from
  (select trunc(add_months(sysdate,-1),'MM') start_date from dual)
  connect by level <= last_day(start_date) - start_date + 1
  )
  );

  l_sql := 'select
            *
            from
            (
            select * from
            (
            SELECT
              thd_custno,
              th_prodno,
              sdate,
              round((sum(th_units)),2) LAST30
            FROM
                                                      (select
                                                          thd_Custno,
                                                          th_prodno,
                                                          th_units,
                                                          TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YY') AS sdate
                                                      from thsdtl@"DB2")
                                                      WHERE thd_custno = '7185' AND SDATE BETWEEN (select trunc(trunc(sysdate,'MM')-1,'MM') from dual) and (select trunc(sysdate,'MM')-1 from dual)
                                                      group by
                                                      thd_custno, th_prodno, sdate
                                                      )
            )
            pivot( max(LAST30) for sdate in('||l_month_day_list||'))';
         
dbms_output.put_line(l_sql);

end;
/




create or replace function my_func return sys_refcursor is

  l_month_day_list varchar2(32767);
  l_sql            varchar2(32767);
  l_ref            sys_refcursor;

begin

  select listagg(col_header,',') within group(order by null)
  into l_month_day_list
  from
  (
  SELECT
  'DATE'''||TO_CHAR(month_day,'RRRR-MM-DD')||''' AS "'||TO_CHAR(month_day,'DD-MON-RR')||'"' col_header
  FROM
  (
  select start_date + rownum - 1 month_day
  from
  (select trunc(add_months(sysdate,-1),'MM') start_date from dual)
  connect by level <= last_day(start_date) - start_date + 1
  )
  );

  l_sql := 'select
            *
            from
            (
            select * from
            (SELECT
              thd_custno,
              th_prodno,
              sdate,
              round((sum(th_units)),2) LAST30
            FROM
                                                      (select
                                                          thd_Custno,
                                                          th_prodno,
                                                          th_units,
                                                          TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YY') AS sdate
                                                      from thsdtl@"DB2")
                                                      WHERE thd_custno = '7185' AND SDATE BETWEEN (select trunc(trunc(sysdate,'MM')-1,'MM') from dual) and (select trunc(sysdate,'MM')-1 from dual)
                                                      group by
                                                      thd_custno, th_prodno, sdate
                                                      )
            )
            pivot( max(LAST30) for sdate in('||l_month_day_list||'))';
         
--dbms_output.put_line(l_sql);
open l_ref for l_sql;

return l_ref;

end;
/