Home » SQL & PL/SQL » SQL & PL/SQL » Reverse row to column and column to row (Oracle 11)
| Reverse row to column and column to row [message #651876] |
Thu, 26 May 2016 12:54  |
 |
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
|
Junior Member |
|
|
Hello,
On picture is result from my store pocedure slc_plancoursepalandweight
procedure slc_plancoursepalandweight(
t_fromdatacourseton date,
t_todatacourseton date,
t_slccourse number,
t_resp out sys_refcursor,
t_error_out out varchar2
)is
--t_frdate date;
begin
--t_frdate:=t_fromdatacourseton+1 ;
open t_resp for
select to_char(ps.today,'DD.MM.YYYY') as today,count(wrr.workday_routing_id) as broiplancouses,nvl(sum(ps.broipaldis),0) as broipaldis ,nvl(sum(ps.wpaldis),0) as teglopaldis ,
nvl(sum(ps.broipalbella),0) as broipalbella ,nvl(sum(ps.wpalbella),0) as teglopalbella ,nvl(sum(ps.broipalipex),0) as broipalipex ,nvl(sum(ps.wpalipex),0) as teglopalipex ,
nvl(sum(ps.broipallogistics),0)as broipallogistics ,nvl(sum(ps.wpallogistics),0) as teglologistics ,
nvl(sum(ps.broipalbellaexport),0) as broipalbellaexport ,nvl(sum(ps.wpalbellaexport),0) as teglobellaexport ,
nvl(sum(ps.broipalreturn),0) as broipalreturn,nvl(sum(ps.wpalreturn),0) as teglopalreturn
from
(select p.today ,case when cn.course_kind_id in (56) then p.workday_routing_id end as workday_routing_id
from (
select wr.workday_routing_id,wr.today,wr.route_id,wr.truck_id
from trt_workday_routing wr
where wr.active=1 and wr.today>= t_fromdatacourseton and wr.today<=t_todatacourseton) p,
(select d.zz as days,c.courses_id as courses_id,c.course_number,c.course_kind_id
from trt_courses c,(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))d
where
((c.active=1 and c.is_in_routing=1 and(c.creation_date<=d.zz+1/2 ) )
or (c.creation_date<=d.zz+1/2 and nvl(c.update_date,to_date('01.01.2100','DD.MM.YYYY'))>=d.zz and c.active=0))
union all
select t.zz as days,sc.status_courses_id as courses_id,c.course_number,c.course_kind_id
from trt_courses c,trt_status_courses sc,(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))t
where sc.courses_id=c.courses_id
and sc.from_data<=t.zz and sc.to_data>t.zz
)cn
where
cn.courses_id=p.route_id
and cn.days=p.today
--and cn.course_kind_id in (222,54)
--group by p.today
order by p.today) wrr,
(select rpi.today,rpi.wrkday_routing_id,SUM(case when rpi.type_pallet_id=0 then rpi.pallet_count else 0 end) as broipaldis,
SUM(case when rpi.type_pallet_id=81 then rpi.pallet_count else 0 end) as broipalbella,
SUM(case when rpi.type_pallet_id=82 then rpi.pallet_count else 0 end) as broipalipex,
SUM(case when rpi.type_pallet_id=83 then rpi.pallet_count else 0 end) as broipallogistics,
SUM(case when rpi.type_pallet_id=84 then rpi.pallet_count else 0 end) as broipalbellaexport,
SUM(case when rpi.type_pallet_id=223 then rpi.pallet_count else 0 end) as broipalreturn,
SUM(case when rpi.type_pallet_id=0 then rpi.weight else 0 end) as wpaldis,
SUM(case when rpi.type_pallet_id=81 then rpi.weight else 0 end) as wpalbella,
SUM(case when rpi.type_pallet_id=82 then rpi.weight else 0 end) as wpalipex,
SUM(case when rpi.type_pallet_id=83 then rpi.weight else 0 end) as wpallogistics,
SUM(case when rpi.type_pallet_id=84 then rpi.weight else 0 end) as wpalbellaexport,
SUM(case when rpi.type_pallet_id=223 then rpi.weight else 0 end) as wpalreturn
from trt_route_pallet_info rpi,
(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))d
where rpi.today=d.zz
and rpi.active=1
group by rpi.today,rpi.wrkday_routing_id) ps
where ps.today=wrr.today and
wrr.workday_routing_id=ps.wrkday_routing_id
group by ps.today
order by ps.today
;
But I want to reverse row with column and column to row but i don`t have idea how to do that .Can you help please?
[mod-edit: code tags added and imaged inserted into message body by bb]
-
Attachment: querry2.jpg
(Size: 70.03KB, Downloaded 1421 times)
[Updated on: Thu, 26 May 2016 13:48] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Reverse row to column and column to row [message #651889 is a reply to message #651876] |
Thu, 26 May 2016 15:06  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have provided a partial demonstration, using two of your columns that have values other than 0. You can expand it to include other columns, using the same technique.
-- If your query (the one inside your procedure), returns results like this:
SCOTT@orcl_12.1.0.2.0> SELECT '15.03.2016' today, 28 broiplancouses, 0 teglopaldis FROM DUAL UNION ALL
2 SELECT '16.03.2016', 25, 3 FROM DUAL UNION ALL
3 SELECT '17.03.2016', 24, 6 FROM DUAL UNION ALL
4 SELECT '18.03.2016', 28, 5 FROM DUAL UNION ALL
5 SELECT '19.03.2016', 20, 5 FROM DUAL UNION ALL
6 SELECT '20.03.2016', 0, 0 FROM DUAL UNION ALL
7 SELECT 'TOTAL', 125, 19 FROM DUAL
8 /
TODAY BROIPLANCOUSES TEGLOPALDIS
---------- -------------- -----------
15.03.2016 28 0
16.03.2016 25 3
17.03.2016 24 6
18.03.2016 28 5
19.03.2016 20 5
20.03.2016 0 0
TOTAL 125 19
7 rows selected.
-- then you can use that query as a sub-query (your_query in the example below) and
-- changes the columns to rows and rows to columns, as shown below:
SCOTT@orcl_12.1.0.2.0> WITH
2 your_query AS
3 (SELECT '15.03.2016' today, 28 broiplancouses, 0 teglopaldis FROM DUAL UNION ALL
4 SELECT '16.03.2016', 25, 3 FROM DUAL UNION ALL
5 SELECT '17.03.2016', 24, 6 FROM DUAL UNION ALL
6 SELECT '18.03.2016', 28, 5 FROM DUAL UNION ALL
7 SELECT '19.03.2016', 20, 5 FROM DUAL UNION ALL
8 SELECT '20.03.2016', 0, 0 FROM DUAL UNION ALL
9 SELECT 'TOTAL', 125, 19 FROM DUAL)
10 SELECT 'BROIPLANCOUSES' former_column,
11 SUM (DECODE (today, '15.03.2016', broiplancouses)) "15.03.2016",
12 SUM (DECODE (today, '16.03.2016', broiplancouses)) "16.03.2016",
13 SUM (DECODE (today, '17.03.2016', broiplancouses)) "17.03.2016",
14 SUM (DECODE (today, '18.03.2016', broiplancouses)) "18.03.2016",
15 SUM (DECODE (today, '19.03.2016', broiplancouses)) "19.03.2016",
16 SUM (DECODE (today, '20.03.2016', broiplancouses)) "20.03.2016",
17 SUM (DECODE (today, 'TOTAL', broiplancouses)) "TOTAL"
18 FROM your_query
19 UNION ALL
20 SELECT 'TEGLOPALDIS',
21 SUM (DECODE (today, '15.03.2016', teglopaldis)) "15.03.2016",
22 SUM (DECODE (today, '16.03.2016', teglopaldis)) "16.03.2016",
23 SUM (DECODE (today, '17.03.2016', teglopaldis)) "17.03.2016",
24 SUM (DECODE (today, '18.03.2016', teglopaldis)) "18.03.2016",
25 SUM (DECODE (today, '19.03.2016', teglopaldis)) "19.03.2016",
26 SUM (DECODE (today, '20.03.2016', teglopaldis)) "29.03.2016",
27 SUM (DECODE (today, 'TOTAL', teglopaldis)) "TOTAL"
28 FROM your_query
29 /
FORMER_COLUMN 15.03.2016 16.03.2016 17.03.2016 18.03.2016 19.03.2016 20.03.2016 TOTAL
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
BROIPLANCOUSES 28 25 24 28 20 0 125
TEGLOPALDIS 0 3 6 5 5 0 19
2 rows selected.
So, you would change your procedure to something like below, adding the additional columns.
I did not test the code below, since I do not have your tables and raw data.
procedure slc_plancoursepalandweight(
t_fromdatacourseton date,
t_todatacourseton date,
t_slccourse number,
t_resp out sys_refcursor,
t_error_out out varchar2
)is
--t_frdate date;
begin
--t_frdate:=t_fromdatacourseton+1 ;
open t_resp for
WITH your_query AS (
select to_char(ps.today,'DD.MM.YYYY') as today,count(wrr.workday_routing_id) as broiplancouses,nvl(sum(ps.broipaldis),0) as broipaldis ,nvl(sum(ps.wpaldis),0) as teglopaldis ,
nvl(sum(ps.broipalbella),0) as broipalbella ,nvl(sum(ps.wpalbella),0) as teglopalbella ,nvl(sum(ps.broipalipex),0) as broipalipex ,nvl(sum(ps.wpalipex),0) as teglopalipex ,
nvl(sum(ps.broipallogistics),0)as broipallogistics ,nvl(sum(ps.wpallogistics),0) as teglologistics ,
nvl(sum(ps.broipalbellaexport),0) as broipalbellaexport ,nvl(sum(ps.wpalbellaexport),0) as teglobellaexport ,
nvl(sum(ps.broipalreturn),0) as broipalreturn,nvl(sum(ps.wpalreturn),0) as teglopalreturn
from
(select p.today ,case when cn.course_kind_id in (56) then p.workday_routing_id end as workday_routing_id
from (
select wr.workday_routing_id,wr.today,wr.route_id,wr.truck_id
from trt_workday_routing wr
where wr.active=1 and wr.today>= t_fromdatacourseton and wr.today<=t_todatacourseton) p,
(select d.zz as days,c.courses_id as courses_id,c.course_number,c.course_kind_id
from trt_courses c,(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))d
where
((c.active=1 and c.is_in_routing=1 and(c.creation_date<=d.zz+1/2 ) )
or (c.creation_date<=d.zz+1/2 and nvl(c.update_date,to_date('01.01.2100','DD.MM.YYYY'))>=d.zz and c.active=0))
union all
select t.zz as days,sc.status_courses_id as courses_id,c.course_number,c.course_kind_id
from trt_courses c,trt_status_courses sc,(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))t
where sc.courses_id=c.courses_id
and sc.from_data<=t.zz and sc.to_data>t.zz
)cn
where
cn.courses_id=p.route_id
and cn.days=p.today
--and cn.course_kind_id in (222,54)
--group by p.today
order by p.today) wrr,
(select rpi.today,rpi.wrkday_routing_id,SUM(case when rpi.type_pallet_id=0 then rpi.pallet_count else 0 end) as broipaldis,
SUM(case when rpi.type_pallet_id=81 then rpi.pallet_count else 0 end) as broipalbella,
SUM(case when rpi.type_pallet_id=82 then rpi.pallet_count else 0 end) as broipalipex,
SUM(case when rpi.type_pallet_id=83 then rpi.pallet_count else 0 end) as broipallogistics,
SUM(case when rpi.type_pallet_id=84 then rpi.pallet_count else 0 end) as broipalbellaexport,
SUM(case when rpi.type_pallet_id=223 then rpi.pallet_count else 0 end) as broipalreturn,
SUM(case when rpi.type_pallet_id=0 then rpi.weight else 0 end) as wpaldis,
SUM(case when rpi.type_pallet_id=81 then rpi.weight else 0 end) as wpalbella,
SUM(case when rpi.type_pallet_id=82 then rpi.weight else 0 end) as wpalipex,
SUM(case when rpi.type_pallet_id=83 then rpi.weight else 0 end) as wpallogistics,
SUM(case when rpi.type_pallet_id=84 then rpi.weight else 0 end) as wpalbellaexport,
SUM(case when rpi.type_pallet_id=223 then rpi.weight else 0 end) as wpalreturn
from trt_route_pallet_info rpi,
(SELECT
(t_todatacourseton - level + 1) AS zz
FROM
dual
CONNECT BY LEVEL <= (t_todatacourseton-t_fromdatacourseton + 1))d
where rpi.today=d.zz
and rpi.active=1
group by rpi.today,rpi.wrkday_routing_id) ps
where ps.today=wrr.today and
wrr.workday_routing_id=ps.wrkday_routing_id
group by ps.today
order by ps.today)
SELECT 'BROIPLANCOUSES' former_column,
SUM (DECODE (today, '15.03.2016', broiplancouses)) "15.03.2016",
SUM (DECODE (today, '16.03.2016', broiplancouses)) "16.03.2016",
SUM (DECODE (today, '17.03.2016', broiplancouses)) "17.03.2016",
SUM (DECODE (today, '18.03.2016', broiplancouses)) "18.03.2016",
SUM (DECODE (today, '19.03.2016', broiplancouses)) "19.03.2016",
SUM (DECODE (today, '20.03.2016', broiplancouses)) "20.03.2016",
SUM (DECODE (today, 'TOTAL', broiplancouses)) "TOTAL"
FROM your_query
UNION ALL
SELECT 'TEGLOPALDIS',
SUM (DECODE (today, '15.03.2016', teglopaldis)) "15.03.2016",
SUM (DECODE (today, '16.03.2016', teglopaldis)) "16.03.2016",
SUM (DECODE (today, '17.03.2016', teglopaldis)) "17.03.2016",
SUM (DECODE (today, '18.03.2016', teglopaldis)) "18.03.2016",
SUM (DECODE (today, '19.03.2016', teglopaldis)) "19.03.2016",
SUM (DECODE (today, '20.03.2016', teglopaldis)) "29.03.2016",
SUM (DECODE (today, 'TOTAL', teglopaldis)) "TOTAL"
FROM your_query;
/
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Reverse row to column and column to row [message #651894 is a reply to message #651893] |
Thu, 26 May 2016 15:51 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example of using a view outside of the procedure, instead of your query inside your procedure, and creating the pivoted query dynamically within the procedure. You would just need to substitute your actual query for the one in the view called your_query below, making any necessary adjustments for the parameters.
-- Create a view using the query from your procedure, instead of the query used in the view below to simulate the results of your query:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE VIEW your_query AS
2 SELECT '15.03.2016' today, 28 broiplancouses, 0 broipaldis, 0 teglopaldis,
3 0 broipalbella, 0 teglopalbella, 48 broipalipex, 76 teglopalipex
4 FROM DUAL UNION ALL
5 SELECT '16.03.2016', 25, 0, 3, 0, 0, 48, 76 FROM DUAL UNION ALL
6 SELECT '17.03.2016', 24, 0, 6, 0, 0, 47, 76 FROM DUAL UNION ALL
7 SELECT '18.03.2016', 28, 0, 5, 0, 0, 44, 76 FROM DUAL UNION ALL
8 SELECT '19.03.2016', 20, 0, 5, 0, 0, 53, 76 FROM DUAL UNION ALL
9 SELECT '20.03.2016', 0, 0, 0, 0, 0, 76, 76 FROM DUAL
10 /
View created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM your_query
2 /
TODAY BROIPLANCOUSES BROIPALDIS TEGLOPALDIS BROIPALBELLA TEGLOPALBELLA BROIPALIPEX TEGLOPALIPEX
---------- -------------- ---------- ----------- ------------ ------------- ----------- ------------
15.03.2016 28 0 0 0 0 48 76
16.03.2016 25 0 3 0 0 48 76
17.03.2016 24 0 6 0 0 47 76
18.03.2016 28 0 5 0 0 44 76
19.03.2016 20 0 5 0 0 53 76
20.03.2016 0 0 0 0 0 76 76
6 rows selected.
-- Create your procedure as below to dynamically create the pivoted query by selecting from the view, returning the results in the ref cursor:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE slc_plancoursepalandweight
2 (t_resp IN OUT SYS_REFCURSOR)
3 AS
4 v_sql VARCHAR2(32767);
5 BEGIN
6 v_sql := 'SELECT ';
7 FOR r_cols IN
8 (SELECT 'broiplancouses' col FROM DUAL UNION ALL
9 SELECT 'broipaldis' FROM DUAL UNION ALL
10 SELECT 'teglopaldis' FROM DUAL UNION ALL
11 SELECT 'broipalbella' FROM DUAL UNION ALL
12 SELECT 'teglopalbella' FROM DUAL UNION ALL
13 SELECT 'broipalipex' FROM DUAL UNION ALL
14 SELECT 'teglopalipex' FROM DUAL)
15 LOOP
16 v_sql := v_sql || '''' || r_cols.col || ''' former_column,';
17 FOR r_dates IN
18 (SELECT DISTINCT today FROM your_query ORDER BY today)
19 LOOP
20 v_sql := v_sql || 'SUM(DECODE(today,''' || r_dates.today || ''',' || r_cols.col || ')) "' || r_dates.today || '",';
21 END LOOP;
22 v_sql := RTRIM (v_sql, ',') || ' FROM your_query UNION ALL SELECT ';
23 END LOOP;
24 v_sql := RTRIM (v_sql, ' UNION ALL SELECT ');
25 OPEN t_resp FOR v_sql;
26 END slc_plancoursepalandweight;
27 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> EXEC slc_plancoursepalandweight (:g_ref)
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> PRINT g_ref
FORMER_COLUMN 15.03.2016 16.03.2016 17.03.2016 18.03.2016 19.03.2016 20.03.2016
-------------- ---------- ---------- ---------- ---------- ---------- ----------
broiplancouses 28 25 24 28 20 0
broipaldis 0 0 0 0 0 0
teglopaldis 0 3 6 5 5 0
broipalbella 0 0 0 0 0 0
teglopalbella 0 0 0 0 0 0
broipalipex 48 48 47 44 53 76
teglopalipex 76 76 76 76 76 76
7 rows selected.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 01:59:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
