Oracle Live SQL - Script: Futuristic SQL Making the impossible possible

Statement 1

select * 
from   co.orders 
join   co.order_items 
using  ( order_id ) 
pivot ( 
  sum ( quantity * unit_price ) 
  for extract ( month from ( order_datetime ) ) 
  in ( 9 sep, 10 oct, 11 nov ) 
)

ORA-01738: missing IN keyword

More Details: https://docs.oracle.com/error-help/db/ora-01738

Statement 2

with rws as (  
  select extract ( month from order_datetime ) mnth, 
         store_id, product_id, customer_id, 
         quantity * unit_price total 
  from   co.orders o 
  join   co.order_items 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
  select *  
  from   rws 
  pivot ( 
    sum ( total ) 
    for mnth in ( 9 sep, 10 oct, 11 nov ) 
  ) 
  order by 1, 2


STORE_ID PRODUCT_ID CUSTOMER_ID SEP OCT NOV
1 2 11 59.1  -  - 
1 2 25  - 118.2  - 
1 2 28 88.65  -  - 
1 2 97 88.65  -  - 
1 2 104  -  - 59.1
1 2 172  -  - 118.2
1 2 173  - 118.2  - 
1 2 187  -  - 118.2
1 2 190  -  - 118.2
1 2 194 118.2  -  - 

Rows 1 - 10. More rows exist.

STORE_ID	PRODUCT_ID	CUSTOMER_ID	SEP	OCT	NOV
1	2	11	59.1	-	-
1	2	25	-	118.2	-
1	2	28	88.65	-	-
1	2	97	88.65	-	-
1	2	104	-	-	59.1
1	2	172	-	-	118.2
1	2	173	-	118.2	-
1	2	187	-	-	118.2
1	2	190	-	-	118.2
1	2	194	118.2	-	-

Statement 3

with rws as (  
  select extract ( month from order_datetime ) mnth, 
         store_id,  
         quantity * unit_price total 
  from   co.orders o 
  join   co.order_items 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
  select *  
  from   rws 
  pivot ( 
    sum ( total ) 
    for mnth in ( 9 sep, 10 oct, 11 nov ) 
  ) 
  order by 1, 2


STORE_ID SEP OCT NOV
1 20393.22 19011.55 16860.97
2 323.57 1222.54 342.42
3 1134.89 427.17 352.54
4 177.44 807.43 492.02
5 988.34 810.04 413.56
6 170.1 850.67 682.26
7 579.49 546.62 552.21
8 1222.48 386.66 668.22
9 268.25 534.27 1026.03
10 44 378.66 1343.4

Rows 1 - 10. More rows exist.

STORE_ID	SEP	OCT	NOV
1	20393.22	19011.55	16860.97
2	323.57	1222.54	342.42
3	1134.89	427.17	352.54
4	177.44	807.43	492.02
5	988.34	810.04	413.56
6	170.1	850.67	682.26
7	579.49	546.62	552.21
8	1222.48	386.66	668.22
9	268.25	534.27	1026.03
10	44	378.66	1343.4

Statement 4

with rws as (  
  select extract ( month from order_datetime ) mnth, 
         store_id,  
         quantity * unit_price total 
  from   co.orders 
  join   co.order_items 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
  select store_id,  
         sum ( sep ), sum ( oct ), sum ( nov ), 
         sum ( sep + oct + nov ) total 
  from   rws 
  pivot ( 
    sum ( total ) 
    for mnth in ( 9 sep, 10 oct, 11 nov ) 
  ) 
  group  by rollup ( store_id ) 
  order by 1, 2


STORE_ID SUM(SEP) SUM(OCT) SUM(NOV) TOTAL
1 20393.22 19011.55 16860.97 56265.74
2 323.57 1222.54 342.42 1888.53
3 1134.89 427.17 352.54 1914.6
4 177.44 807.43 492.02 1476.89
5 988.34 810.04 413.56 2211.94
6 170.1 850.67 682.26 1703.03
7 579.49 546.62 552.21 1678.32
8 1222.48 386.66 668.22 2277.36
9 268.25 534.27 1026.03 1828.55
10 44 378.66 1343.4 1766.06

Rows 1 - 10. More rows exist.

STORE_ID	SUM(SEP)	SUM(OCT)	SUM(NOV)	TOTAL
1	20393.22	19011.55	16860.97	56265.74
2	323.57	1222.54	342.42	1888.53
3	1134.89	427.17	352.54	1914.6
4	177.44	807.43	492.02	1476.89
5	988.34	810.04	413.56	2211.94
6	170.1	850.67	682.26	1703.03
7	579.49	546.62	552.21	1678.32
8	1222.48	386.66	668.22	2277.36
9	268.25	534.27	1026.03	1828.55
10	44	378.66	1343.4	1766.06

Statement 5

with rws as (  
  select extract ( month from o.order_datetime ) mnth, 
         o.store_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
), grps as ( 
  select case grouping ( mnth ) 
           when 0 then mnth 
           else 13 
         end mth,  
         case grouping ( store_id ) 
           when 0 then 'Store ' || store_id 
           else 'All stores' 
         end store_id,  
         sum ( total ) total, 
         grouping ( store_id ) store_gid, 
         grouping ( mnth ) mnth_gid 
  from   rws 
  group by cube ( mnth, store_id ) 
) 
  select * from grps


MTH STORE_ID TOTAL STORE_GID MNTH_GID
13 All stores 77697.7 1 1
13 Store 1 56265.74 0 1
13 Store 2 1888.53 0 1
13 Store 3 1914.6 0 1
13 Store 4 1476.89 0 1
13 Store 5 2211.94 0 1
13 Store 6 1703.03 0 1
13 Store 7 1678.32 0 1
13 Store 8 2277.36 0 1
13 Store 9 1828.55 0 1

Rows 1 - 10. More rows exist.

MTH	STORE_ID	TOTAL	STORE_GID	MNTH_GID
13	All stores	77697.7	1	1
13	Store 1	56265.74	0	1
13	Store 2	1888.53	0	1
13	Store 3	1914.6	0	1
13	Store 4	1476.89	0	1
13	Store 5	2211.94	0	1
13	Store 6	1703.03	0	1
13	Store 7	1678.32	0	1
13	Store 8	2277.36	0	1
13	Store 9	1828.55	0	1

Statement 6

with rws as (  
  select extract ( month from o.order_datetime ) mnth, 
         o.store_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
), grps as ( 
  select case grouping ( mnth ) 
           when 0 then mnth 
           else 13 
         end mth,  
         case grouping ( store_id ) 
           when 0 then 'Store ' || store_id 
           else 'All stores' 
         end store_id,  
         sum ( total ) total 
  from   rws 
  group by cube ( mnth, store_id ) 
) 
  select * from grps 
  pivot ( 
    sum ( total ) 
    for mth in ( 9 sep, 10 oct, 11 nov, 13 total ) 
  ) 
  order  by to_number ( regexp_substr ( store_id, '[0-9]+' ) )


STORE_ID SEP OCT NOV TOTAL
Store 1 20393.22 19011.55 16860.97 56265.74
Store 2 323.57 1222.54 342.42 1888.53
Store 3 1134.89 427.17 352.54 1914.6
Store 4 177.44 807.43 492.02 1476.89
Store 5 988.34 810.04 413.56 2211.94
Store 6 170.1 850.67 682.26 1703.03
Store 7 579.49 546.62 552.21 1678.32
Store 8 1222.48 386.66 668.22 2277.36
Store 9 268.25 534.27 1026.03 1828.55
Store 10 44 378.66 1343.4 1766.06

Rows 1 - 10. More rows exist.

STORE_ID	SEP	OCT	NOV	TOTAL
Store 1	20393.22	19011.55	16860.97	56265.74
Store 2	323.57	1222.54	342.42	1888.53
Store 3	1134.89	427.17	352.54	1914.6
Store 4	177.44	807.43	492.02	1476.89
Store 5	988.34	810.04	413.56	2211.94
Store 6	170.1	850.67	682.26	1703.03
Store 7	579.49	546.62	552.21	1678.32
Store 8	1222.48	386.66	668.22	2277.36
Store 9	268.25	534.27	1026.03	1828.55
Store 10	44	378.66	1343.4	1766.06

Statement 7

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total, 
         o.order_datetime 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
), grps as ( 
  select case grouping ( dy ) 
           when 0 then dy 
           else 'All' 
         end dy,  
         case grouping ( product_id ) 
           when 0 then 'Product ' || product_id 
           else 'All stores' 
         end product_id,  
         sum ( total ) total 
  from   rws 
  group by cube ( dy, product_id ) 
) 
  select * from grps 
  pivot ( 
    sum ( total ) 
    for dy in (  
       'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun', 'All' 
    ) 
  ) 
  order  by to_number ( regexp_substr ( product_id, '[0-9]+' ) )


PRODUCT_ID 'Mon' 'Tue' 'Wed' 'Thu' 'Fri' 'Sat' 'Sun' 'All'
Product 2  -  -  -  - 88.65  -  - 88.65
Product 4  - 220.85  -  -  - 176.68  - 397.53
Product 5 131.13  - 131.13  - 174.84 174.84  - 611.94
Product 7  -  -  -  - 76.64  -  - 76.64
Product 8 58.74  -  -  -  -  -  - 58.74
Product 9  -  -  -  -  - 42.32  - 42.32
Product 10  -  -  -  - 58.98  -  - 58.98
Product 11 30.69  - 122.76  -  -  -  - 153.45
Product 12  -  -  - 41.92  -  -  - 41.92
Product 13  - 50.56  -  - 12.64 50.56  - 113.76

Rows 1 - 10. More rows exist.

PRODUCT_ID	'Mon'	'Tue'	'Wed'	'Thu'	'Fri'	'Sat'	'Sun'	'All'
Product 2	-	-	-	-	88.65	-	-	88.65
Product 4	-	220.85	-	-	-	176.68	-	397.53
Product 5	131.13	-	131.13	-	174.84	174.84	-	611.94
Product 7	-	-	-	-	76.64	-	-	76.64
Product 8	58.74	-	-	-	-	-	-	58.74
Product 9	-	-	-	-	-	42.32	-	42.32
Product 10	-	-	-	-	58.98	-	-	58.98
Product 11	30.69	-	122.76	-	-	-	-	153.45
Product 12	-	-	-	41.92	-	-	-	41.92
Product 13	-	50.56	-	-	12.64	50.56	-	113.76

Statement 8

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total, 
         o.order_datetime 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
), grps as ( 
  select case grouping ( dy ) 
           when 0 then dy 
           else 'All' 
         end dy,  
         case grouping ( product_id ) 
           when 0 then 'Product ' || product_id 
           else 'All stores' 
         end product_id,  
         sum ( total ) total 
  from   rws 
  group by cube ( dy, product_id ) 
) 
  select product_id, g.dy_xml.getStringVal()  
  from   grps  
  pivot xml ( 
    sum ( total ) 
    for dy in ( any ) 
  ) g 
  order  by to_number ( regexp_substr ( product_id, '[0-9]+' ) )


PRODUCT_ID G.DY_XML.GETSTRINGVAL()
Product 2 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">88.65</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">88.65</column></item></PivotSet>
Product 4 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">397.53</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">176.68</column></item><item><column name = "DY">Tue</column><column name = "SUM(TOTAL)">220.85</column></item></PivotSet>
Product 5 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">611.94</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">174.84</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">131.13</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">174.84</column></item><item><column name = "DY">Wed</column><column name = "SUM(TOTAL)">131.13</column></item></PivotSet>
Product 7 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">76.64</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">76.64</column></item></PivotSet>
Product 8 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">58.74</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">58.74</column></item></PivotSet>
Product 9 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">42.32</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">42.32</column></item></PivotSet>
Product 10 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">58.98</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">58.98</column></item></PivotSet>
Product 11 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">153.45</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">30.69</column></item><item><column name = "DY">Wed</column><column name = "SUM(TOTAL)">122.76</column></item></PivotSet>
Product 12 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">41.92</column></item><item><column name = "DY">Thu</column><column name = "SUM(TOTAL)">41.92</column></item></PivotSet>
Product 13 <PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">113.76</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">12.64</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">50.56</column></item><item><column name = "DY">Tue</column><column name = "SUM(TOTAL)">50.56</column></item></PivotSet>

Rows 1 - 10. More rows exist.

PRODUCT_ID	G.DY_XML.GETSTRINGVAL()
Product 2	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">88.65</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">88.65</column></item></PivotSet>
Product 4	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">397.53</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">176.68</column></item><item><column name = "DY">Tue</column><column name = "SUM(TOTAL)">220.85</column></item></PivotSet>
Product 5	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">611.94</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">174.84</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">131.13</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">174.84</column></item><item><column name = "DY">Wed</column><column name = "SUM(TOTAL)">131.13</column></item></PivotSet>
Product 7	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">76.64</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">76.64</column></item></PivotSet>
Product 8	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">58.74</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">58.74</column></item></PivotSet>
Product 9	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">42.32</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">42.32</column></item></PivotSet>
Product 10	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">58.98</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">58.98</column></item></PivotSet>
Product 11	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">153.45</column></item><item><column name = "DY">Mon</column><column name = "SUM(TOTAL)">30.69</column></item><item><column name = "DY">Wed</column><column name = "SUM(TOTAL)">122.76</column></item></PivotSet>
Product 12	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">41.92</column></item><item><column name = "DY">Thu</column><column name = "SUM(TOTAL)">41.92</column></item></PivotSet>
Product 13	<PivotSet><item><column name = "DY">All</column><column name = "SUM(TOTAL)">113.76</column></item><item><column name = "DY">Fri</column><column name = "SUM(TOTAL)">12.64</column></item><item><column name = "DY">Sat</column><column name = "SUM(TOTAL)">50.56</column></item><item><column name = "DY">Tue</column><column name = "SUM(TOTAL)">50.56</column></item></PivotSet>

Statement 9

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
), ranked_rows as ( 
  select dense_rank () over ( order by dy ) dr, 
         product_id, total 
  from   rws 
), grps as ( 
  select case grouping ( dr ) 
           when 0 then dr 
           else 8 
         end dr,  
         case grouping ( product_id ) 
           when 0 then 'Product ' || product_id 
           else 'All stores' 
         end product_id,  
         sum ( total ) total 
  from   ranked_rows 
  group by cube ( dr, product_id ) 
) 
  select * 
  from   grps  
  pivot ( 
    sum ( total ) 
    for dr in (  
       1, 2, 3, 4, 5, 6, 7, 8 
    ) 
  ) g 
  order  by to_number ( regexp_substr ( product_id, '[0-9]+' ) )


PRODUCT_ID 1 2 3 4 5 6 7 8
Product 2 88.65  -  -  -  -  -  - 88.65
Product 4  -  - 176.68  -  - 220.85  - 397.53
Product 5 174.84 131.13 174.84  -  -  - 131.13 611.94
Product 7 76.64  -  -  -  -  -  - 76.64
Product 8  - 58.74  -  -  -  -  - 58.74
Product 9  -  - 42.32  -  -  -  - 42.32
Product 10 58.98  -  -  -  -  -  - 58.98
Product 11  - 30.69  -  -  -  - 122.76 153.45
Product 12  -  -  -  - 41.92  -  - 41.92
Product 13 12.64  - 50.56  -  - 50.56  - 113.76

Rows 1 - 10. More rows exist.

PRODUCT_ID	1	2	3	4	5	6	7	8
Product 2	88.65	-	-	-	-	-	-	88.65
Product 4	-	-	176.68	-	-	220.85	-	397.53
Product 5	174.84	131.13	174.84	-	-	-	131.13	611.94
Product 7	76.64	-	-	-	-	-	-	76.64
Product 8	-	58.74	-	-	-	-	-	58.74
Product 9	-	-	42.32	-	-	-	-	42.32
Product 10	58.98	-	-	-	-	-	-	58.98
Product 11	-	30.69	-	-	-	-	122.76	153.45
Product 12	-	-	-	-	41.92	-	-	41.92
Product 13	12.64	-	50.56	-	-	50.56	-	113.76

Statement 10

create or replace function rank_rows (  
  table_name  dbms_tf.table_t, 
  rank_column dbms_tf.columns_t 
) return clob sql_macro as 
  stmt clob; 
begin 
 
  stmt := ' 
  select dense_rank () over ( order by ' || rank_column ( 1 ) || ' ) dr, t.* 
  from   table_name t'; 
 
  return stmt; 
end rank_rows;

Function created.

Statement 11

select * from rank_rows ( 
  hr.employees,  
  columns ( department_id ) 
)


DR EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
1 200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400  - 101 10
2 201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000  - 100 20
2 202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000  - 201 20
3 114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30
3 115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30
3 116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30
3 117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30
3 118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30
3 119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114 30
4 203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500  - 101 40

Rows 1 - 10. More rows exist.

DR	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
1	200	Jennifer	Whalen	JWHALEN	515.123.4444	17-SEP-03	AD_ASST	4400	-	101	10
2	201	Michael	Hartstein	MHARTSTE	515.123.5555	17-FEB-04	MK_MAN	13000	-	100	20
2	202	Pat	Fay	PFAY	603.123.6666	17-AUG-05	MK_REP	6000	-	201	20
3	114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30
3	115	Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30
3	116	Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30
3	117	Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30
3	118	Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30
3	119	Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114	30
4	203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	6500	-	101	40

Statement 12

create or replace function except_cols ( 
  tab         dbms_tf.table_t, 
  except_cols dbms_tf.columns_t 
) return clob sql_macro is 
  sql_stmt    clob; 
  column_list clob; 
begin 
 
  for col in tab.column.first .. tab.column.last  
  loop 
    if  tab.column ( col ).description.name  
      not member of except_cols then 
      column_list := column_list || ',' || tab.column ( col ).description.name; 
    end if; 
  end loop; 
   
  sql_stmt :=  
    'select ' ||  
    trim ( both ',' from column_list ) ||  
    ' from tab'; 
 
  return sql_stmt; 
   
end except_cols;

Function created.

Statement 13

select * from except_cols (  
  hr.employees,  
  columns ( hire_date, salary, commission_pct, email, phone_number )  
)


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID DEPARTMENT_ID
100 Steven King AD_PRES  - 90
101 Neena Kochhar AD_VP 100 90
102 Lex De Haan AD_VP 100 90
103 Alexander Hunold IT_PROG 102 60
104 Bruce Ernst IT_PROG 103 60
105 David Austin IT_PROG 103 60
106 Valli Pataballa IT_PROG 103 60
107 Diana Lorentz IT_PROG 103 60
108 Nancy Greenberg FI_MGR 101 100
109 Daniel Faviet FI_ACCOUNT 108 100

Rows 1 - 10. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	JOB_ID	MANAGER_ID	DEPARTMENT_ID
100	Steven	King	AD_PRES	-	90
101	Neena	Kochhar	AD_VP	100	90
102	Lex	De Haan	AD_VP	100	90
103	Alexander	Hunold	IT_PROG	102	60
104	Bruce	Ernst	IT_PROG	103	60
105	David	Austin	IT_PROG	103	60
106	Valli	Pataballa	IT_PROG	103	60
107	Diana	Lorentz	IT_PROG	103	60
108	Nancy	Greenberg	FI_MGR	101	100
109	Daniel	Faviet	FI_ACCOUNT	108	100

Statement 14

create or replace function replace_col_with_rank (  
  table_name  dbms_tf.table_t, 
  rank_column dbms_tf.columns_t 
) return clob sql_macro as 
  stmt clob; 
begin 
 
  stmt := ' 
  with rws as ( 
    select dense_rank () over ( order by ' || rank_column ( 1 ) || ' ) dr, t.* 
    from   table_name t 
  ) 
  select * 
  from   except_cols ( rws, columns ( ' || rank_column (1) || ') )'; 
 
  return stmt; 
end replace_col_with_rank;

Function created.

Statement 15

select * from replace_col_with_rank ( 
  hr.employees, columns ( department_id ) 
)


DR EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID
1 200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400  - 101
2 201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000  - 100
2 202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000  - 201
3 114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100
3 115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114
3 116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114
3 117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114
3 118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114
3 119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114
4 203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500  - 101

Rows 1 - 10. More rows exist.

DR	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID
1	200	Jennifer	Whalen	JWHALEN	515.123.4444	17-SEP-03	AD_ASST	4400	-	101
2	201	Michael	Hartstein	MHARTSTE	515.123.5555	17-FEB-04	MK_MAN	13000	-	100
2	202	Pat	Fay	PFAY	603.123.6666	17-AUG-05	MK_REP	6000	-	201
3	114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100
3	115	Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114
3	116	Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114
3	117	Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114
3	118	Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114
3	119	Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114
4	203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	6500	-	101

Statement 16

select * from replace_col_with_rank ( 
  hr.departments, columns ( location_id ) 
)


DR DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID
1 60 IT 103
2 50 Shipping 121
3 30 Purchasing 114
3 10 Administration 200
3 140 Control And Credit  - 
3 260 Recruiting  - 
3 250 Retail Sales  - 
3 240 Government Sales  - 
3 90 Executive 100
3 100 Finance 108

Rows 1 - 10. More rows exist.

DR	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID
1	60	IT	103
2	50	Shipping	121
3	30	Purchasing	114
3	10	Administration	200
3	140	Control And Credit	-
3	260	Recruiting	-
3	250	Retail Sales	-
3	240	Government Sales	-
3	90	Executive	100
3	100	Finance	108

Statement 17

declare 
  l_clob clob; 
begin 
  dbms_utility.expand_sql_text ( 
    input_sql_text  =>  
      q'! 
select * from replace_col_with_rank ( 
  hr.departments, columns ( location_id ) 
)!', 
    output_sql_text => l_clob  ); 
  dbms_output.put_line(l_clob); 
end;

Statement processed.
SELECT "A1"."DR" "DR","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."MANAGER_ID" "MANAGER_ID" FROM  (SELECT "A5"."DR" "DR","A5"."DEPARTMENT_ID" "DEPARTMENT_ID","A5"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A5"."MANAGER_ID" "MANAGER_ID" FROM  (SELECT "A6"."DR" "DR","A6"."DEPARTMENT_ID" "DEPARTMENT_ID","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A6"."MANAGER_ID" "MANAGER_ID" FROM  (SELECT "A7"."DR" "DR","A7"."DEPARTMENT_ID" "DEPARTMENT_ID","A7"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A7"."MANAGER_ID" "MANAGER_ID" FROM  (SELECT "A8"."DR" "DR","A8"."DEPARTMENT_ID" "DEPARTMENT_ID","A8"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A8"."MANAGER_ID" "MANAGER_ID" FROM  (SELECT "A2"."DR" "DR","A2"."DEPARTMENT_ID" "DEPARTMENT_ID","A2"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A2"."MANAGER_ID" "MANAGER_ID","A2"."LOCATION_ID" "LOCATION_ID" FROM  (SELECT DENSE_RANK() OVER ( ORDER BY "A3"."LOCATION_ID") "DR","A3"."DEPARTMENT_ID" "DEPARTMENT_ID","A3"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A3"."MANAGER_ID" "MANAGER_ID","A3"."LOCATION_ID" "LOCATION_ID" FROM  (SELECT "A4"."DEPARTMENT_ID" "DEPARTMENT_ID","A4"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A4"."MANAGER_ID" "MANAGER_ID","A4"."LOCATION_ID" "LOCATION_ID" FROM "HR"."DEPARTMENTS" "A4") "A3") "A2") "A8") "A7") "A6") "A5") "A1"

Statement 18

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
  select * from replace_col_with_rank (   
    rws, columns ( dy ) 
  ) 
  pivot ( 
    sum ( total ) 
    for dr in ( 1, 2, 3, 4, 5, 6, 7, 8 ) 
  )


PRODUCT_ID 1 2 3 4 5 6 7 8
27 119.73  -  -  -  -  -  -  - 
14 182.98  -  -  -  -  -  -  - 
23  -  -  -  -  -  - 41.32  - 
40 170.3  -  - 136.24  -  -  -  - 
7 76.64  -  -  -  -  -  -  - 
15 13.97  - 27.94  -  -  -  -  - 
42  -  -  -  -  - 30.33  -  - 
31  -  -  -  -  - 114.36  -  - 
36  -  -  -  -  -  - 196.48  - 
41 34.64  -  - 34.64 17.32  -  -  - 

Rows 1 - 10. More rows exist.

PRODUCT_ID	1	2	3	4	5	6	7	8
27	119.73	-	-	-	-	-	-	-
14	182.98	-	-	-	-	-	-	-
23	-	-	-	-	-	-	41.32	-
40	170.3	-	-	136.24	-	-	-	-
7	76.64	-	-	-	-	-	-	-
15	13.97	-	27.94	-	-	-	-	-
42	-	-	-	-	-	30.33	-	-
31	-	-	-	-	-	114.36	-	-
36	-	-	-	-	-	-	196.48	-
41	34.64	-	-	34.64	17.32	-	-	-

Statement 19

create or replace function add_subtotals (  
  table_name   dbms_tf.table_t, 
  pivot_col    dbms_tf.columns_t, 
  rows_col     dbms_tf.columns_t, 
  subtotal_val dbms_tf.columns_t 
) return clob sql_macro as 
  select_subtotals clob; 
  cube_columns clob; 
  stmt clob; 
begin 
  for c in 1 .. rows_col.count loop 
    select_subtotals := select_subtotals ||  
      'case grouping ( ' || rows_col (c) || ' ) 
         when 0 then to_char ( ' || rows_col (c) || ' ) 
         else ''Total'' 
       end ' || rows_col (c) || ','; 
    cube_columns := cube_columns || rows_col (c) || ','; 
  end loop; 
 
  select_subtotals := select_subtotals || 'case grouping ( dr ) 
         when 0 then to_char ( dr ) 
         else ''Total'' 
       end dr'; 
  cube_columns := cube_columns || ' dr '; 
   
  stmt := ' 
  select ' || select_subtotals || ',  
         sum ( ' || subtotal_val (1) || ' ) total 
  from   replace_col_with_rank ( table_name, columns ( ' || pivot_col ( 1 ) || ' ) )  
  group by cube ( ' || cube_columns || ' )'; 
   
  return stmt; 
end add_subtotals;

Function created.

Statement 20

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from add_subtotals ( 
  rws, columns ( dy ), columns ( product_id ), columns ( total ) 
)


PRODUCT_ID DR TOTAL
Total Total 5632.37
Total 1 1666.31
Total 2 738.78
Total 3 699.45
Total 4 318.94
Total 5 635.92
Total 6 702.97
Total 7 870
2 Total 88.65
2 1 88.65

Rows 1 - 10. More rows exist.

PRODUCT_ID	DR	TOTAL
Total	Total	5632.37
Total	1	1666.31
Total	2	738.78
Total	3	699.45
Total	4	318.94
Total	5	635.92
Total	6	702.97
Total	7	870
2	Total	88.65
2	1	88.65

Statement 21

create or replace function cross_table_pivot (  
  table_name  dbms_tf.table_t, 
  pivot_col   dbms_tf.columns_t, 
  rows_col    dbms_tf.columns_t, 
  pivot_val   dbms_tf.columns_t, 
  num_pivot_cols integer 
) return clob sql_macro as 
  subtotal_columns clob; 
  pivot_columns clob; 
  row_columns clob; 
  select_list clob; 
  stmt clob; 
begin 
  for i in 1 .. rows_col.count loop 
    row_columns := row_columns || rows_col(i) || ','; 
  end loop; 
   
  select_list := row_columns; 
   
  for i in 1 .. num_pivot_cols loop 
    pivot_columns := pivot_columns || i || ' val' || i || ','; 
    select_list   := select_list || ' nvl ( val' || i || ', 0 ) as val' || i || ','; 
  end loop; 
   
  pivot_columns := pivot_columns || q'< 'Total' as Total >'; 
  row_columns   := rtrim ( row_columns, ',' ); 
  select_list   := select_list || ' nvl ( total, 0 ) as total '; 
   
  stmt := ' 
  select ' || select_list || ' 
  from   add_subtotals (  
      table_name,  
      columns ( ' || pivot_col (1) || ' ),  
      columns ( ' || row_columns || ' ),  
      columns ( ' || pivot_val (1) || ' ) 
  )  
  pivot ( 
    sum ( total )  
    for dr 
    in ( ' || pivot_columns || ' ) 
  ) '; 
     
  return stmt; 
end cross_table_pivot;

Function created.

Statement 22

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( dy ),  
  rows_col   => columns ( product_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => 7 
) 
order  by 1


PRODUCT_ID VAL1 VAL2 VAL3 VAL4 VAL5 VAL6 VAL7 TOTAL
10 58.98 0 0 0 0 0 0 58.98
11 0 30.69 0 0 0 0 122.76 153.45
12 0 0 0 0 41.92 0 0 41.92
13 12.64 0 50.56 0 0 50.56 0 113.76
14 182.98 0 0 0 0 0 0 182.98
15 13.97 0 27.94 0 0 0 0 41.91
16 52.36 0 0 0 0 0 0 52.36
17 119.67 0 0 0 0 0 0 119.67
18 48.92 0 0 73.38 0 0 0 122.3
19 71.7 0 0 0 0 0 0 71.7

Rows 1 - 10. More rows exist.

PRODUCT_ID	VAL1	VAL2	VAL3	VAL4	VAL5	VAL6	VAL7	TOTAL
10	58.98	0	0	0	0	0	0	58.98
11	0	30.69	0	0	0	0	122.76	153.45
12	0	0	0	0	41.92	0	0	41.92
13	12.64	0	50.56	0	0	50.56	0	113.76
14	182.98	0	0	0	0	0	0	182.98
15	13.97	0	27.94	0	0	0	0	41.91
16	52.36	0	0	0	0	0	0	52.36
17	119.67	0	0	0	0	0	0	119.67
18	48.92	0	0	73.38	0	0	0	122.3
19	71.7	0	0	0	0	0	0	71.7

Statement 23

with rws as (  
  select to_char ( order_datetime, 'Mon' ) mnth, 
         store_id,  
         quantity * unit_price total 
  from   co.orders o 
  join   co.order_items 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( mnth ),  
  rows_col   => columns ( store_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => 3 
) 
order  by 1


STORE_ID VAL1 VAL2 VAL3 TOTAL
1 16860.97 19011.55 20393.22 56265.74
10 1343.4 378.66 44 1766.06
11 545.9 660.27 588.22 1794.39
12 707.91 136 472.46 1316.37
13 224.75 0 0 224.75
14 683.07 151 0 834.07
15 517.1 0 0 517.1
2 342.42 1222.54 323.57 1888.53
3 352.54 427.17 1134.89 1914.6
4 492.02 807.43 177.44 1476.89

Rows 1 - 10. More rows exist.

STORE_ID	VAL1	VAL2	VAL3	TOTAL
1	16860.97	19011.55	20393.22	56265.74
10	1343.4	378.66	44	1766.06
11	545.9	660.27	588.22	1794.39
12	707.91	136	472.46	1316.37
13	224.75	0	0	224.75
14	683.07	151	0	834.07
15	517.1	0	0	517.1
2	342.42	1222.54	323.57	1888.53
3	352.54	427.17	1134.89	1914.6
4	492.02	807.43	177.44	1476.89

Statement 24

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( dy ),  
  rows_col   => columns ( product_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => 2  
) 
order  by 1


PRODUCT_ID VAL1 VAL2 TOTAL
10 58.98 0 58.98
11 0 30.69 153.45
12 0 0 41.92
13 12.64 0 113.76
14 182.98 0 182.98
15 13.97 0 41.91
16 52.36 0 52.36
17 119.67 0 119.67
18 48.92 0 122.3
19 71.7 0 71.7

Rows 1 - 10. More rows exist.

PRODUCT_ID	VAL1	VAL2	TOTAL
10	58.98	0	58.98
11	0	30.69	153.45
12	0	0	41.92
13	12.64	0	113.76
14	182.98	0	182.98
15	13.97	0	41.91
16	52.36	0	52.36
17	119.67	0	119.67
18	48.92	0	122.3
19	71.7	0	71.7

Statement 25

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id,  
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( dy ),  
  rows_col   => columns ( product_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => :pivot_cols 
) 
order  by 1

ORA-62565: The SQL Macro method failed with error(s). 
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SQL_MGBQERGGXJTXJEQSIFHIQLYAQ.CROSS_TABLE_PIVOT", line 23
ORA-06512: at line 6

More Details: https://docs.oracle.com/error-help/db/ora-62565

Statement 26

with rws as (  
  select to_char ( o.order_datetime, 'Dy' ) dy, 
         oi.product_id, o.store_id, 
         nvl ( quantity * unit_price, 0 ) total 
  from   co.orders o 
  join   co.order_items oi 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-11-24 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( dy ),  
  rows_col   => columns ( product_id, store_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => 7 
) 
order  by 1


PRODUCT_ID STORE_ID VAL1 VAL2 VAL3 VAL4 VAL5 VAL6 VAL7 TOTAL
10 10 58.98 0 0 0 0 0 0 58.98
10 Total 58.98 0 0 0 0 0 0 58.98
11 1 0 30.69 0 0 0 0 0 30.69
11 2 0 0 0 0 0 0 122.76 122.76
11 Total 0 30.69 0 0 0 0 122.76 153.45
12 1 0 0 0 0 41.92 0 0 41.92
12 Total 0 0 0 0 41.92 0 0 41.92
13 1 12.64 0 0 0 0 0 0 12.64
13 10 0 0 0 0 0 50.56 0 50.56
13 15 0 0 50.56 0 0 0 0 50.56

Rows 1 - 10. More rows exist.

PRODUCT_ID	STORE_ID	VAL1	VAL2	VAL3	VAL5	VAL6	VAL7	TOTAL
10	10	58.98	0	0	0	0	0	58.98
10	Total	58.98	0	0	0	0	0	58.98
11	1	0	30.69	0	0	0	0	30.69
11	2	0	0	0	0	0	122.76	122.76
11	Total	0	30.69	0	0	0	122.76	153.45
12	1	0	0	0	41.92	0	0	41.92
12	Total	0	0	0	41.92	0	0	41.92
13	1	12.64	0	0	0	0	0	12.64
13	10	0	0	0	0	50.56	0	50.56
13	15	0	0	50.56	0	0	0	50.56

Statement 27

with rws as (  
  select extract ( year from hire_date ) yr, salary, department_id 
  from   hr.employees 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( yr ),  
  rows_col   => columns ( department_id ),  
  pivot_val  => columns ( salary ),  
  num_pivot_cols => 8 
)


DEPARTMENT_ID VAL1 VAL2 VAL3 VAL4 VAL5 VAL6 VAL7 VAL8 TOTAL
20 0 0 0 13000 6000 0 0 0 19000
50 0 0 15000 19500 48200 37800 26100 9800 156400
60 0 0 0 0 4800 13800 10200 0 28800
90 17000 0 24000 0 17000 0 0 0 58000
30 0 11000 3100 0 5700 2600 2500 0 24900
 - 0 0 0 0 0 0 7000 0 7000
80 0 0 0 53500 100300 59100 42200 49400 304500
10 0 0 4400 0 0 0 0 0 4400
40 0 6500 0 0 0 0 0 0 6500
100 0 21008 0 0 15900 7800 6900 0 51608

Rows 1 - 10. More rows exist.

DEPARTMENT_ID	VAL1	VAL2	VAL3	VAL4	VAL5	VAL6	VAL7	VAL8	TOTAL
20	0	0	0	13000	6000	0	0	0	19000
50	0	0	15000	19500	48200	37800	26100	9800	156400
60	0	0	0	0	4800	13800	10200	0	28800
90	17000	0	24000	0	17000	0	0	0	58000
30	0	11000	3100	0	5700	2600	2500	0	24900
-	0	0	0	0	0	0	7000	0	7000
80	0	0	0	53500	100300	59100	42200	49400	304500
10	0	0	4400	0	0	0	0	0	4400
40	0	6500	0	0	0	0	0	0	6500
100	0	21008	0	0	15900	7800	6900	0	51608

Statement 28

create or replace function add_subtotals (  
  table_name   dbms_tf.table_t, 
  pivot_col  dbms_tf.columns_t, 
  rows_col     dbms_tf.columns_t, 
  subtotal_val dbms_tf.columns_t 
) return clob sql_macro as 
  select_subtotals clob; 
  cube_columns clob; 
  stmt clob; 
begin 
  for c in 1 .. rows_col.count loop 
    select_subtotals := select_subtotals ||  
      'case grouping ( ' || rows_col (c) || ' ) 
         when 0 then to_char ( ' || rows_col (c) || ' ) 
         else ''Total'' 
       end ' || rows_col (c) || ','; 
    cube_columns := cube_columns || rows_col (c) || ','; 
  end loop; 
 
  select_subtotals := select_subtotals || ' case grouping ( dr ) 
         when 0 then to_char ( dr ) 
         else ''Total'' 
       end dr, ' || pivot_col ( 1 ); 
  cube_columns := cube_columns || ' dr, ' || pivot_col ( 1 ); 
   
  stmt := ' 
  select ' || select_subtotals || ',  
         sum ( ' || subtotal_val (1) || ' ) total 
  from   rank_rows ( table_name, columns ( ' || pivot_col ( 1 ) || ' ) )  
  group by cube ( ' || cube_columns || ' )'; 
   
  return stmt; 
end add_subtotals;

Function created.

Statement 29

create or replace function cross_table_pivot (  
  table_name  dbms_tf.table_t, 
  pivot_col   dbms_tf.columns_t, 
  rows_col    dbms_tf.columns_t, 
  pivot_val   dbms_tf.columns_t, 
  num_pivot_cols integer 
) return clob sql_macro as 
  subtotal_columns clob; 
  pivot_columns clob; 
  row_columns clob; 
  select_list clob; 
  stmt clob; 
begin 
  for i in 1 .. rows_col.count loop 
    row_columns := row_columns || rows_col(i) || ','; 
  end loop; 
   
  select_list := row_columns; 
   
  for i in 1 .. num_pivot_cols loop 
    pivot_columns := pivot_columns || i || ' val' || i || ','; 
    select_list   := select_list || ' nvl ( val' || i || ', 0 ) as val' || i || ','; 
  end loop; 
   
  pivot_columns := pivot_columns || q'< 'Total' as Total >'; 
  row_columns   := rtrim ( row_columns, ',' ); 
  select_list   := select_list || ' nvl ( total, 0 ) as total '; 
   
  stmt := ' 
  select * 
  from   add_subtotals (  
      table_name,  
      columns ( ' || pivot_col (1) || ' ),  
      columns ( ' || row_columns || ' ),  
      columns ( ' || pivot_val (1) || ' ) 
  )  
  pivot ( 
    sum ( total ) val, max ( ' || pivot_col (1) || ' ) col  
    for dr 
    in ( ' || pivot_columns || ' ) 
  ) '; 
     
  return stmt; 
end cross_table_pivot;

Function created.

Statement 30

with rws as (  
  select extract ( year from hire_date ) yr, salary, department_id 
  from   hr.employees 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( yr ),  
  rows_col   => columns ( department_id ),  
  pivot_val  => columns ( salary ),  
  num_pivot_cols => 8 
)


DEPARTMENT_ID VAL1_VAL VAL1_COL VAL2_VAL VAL2_COL VAL3_VAL VAL3_COL VAL4_VAL VAL4_COL VAL5_VAL VAL5_COL VAL6_VAL VAL6_COL VAL7_VAL VAL7_COL VAL8_VAL VAL8_COL TOTAL_VAL TOTAL_COL
20  -  -  -  -  -  - 26000 2004 12000 2005  -  -  -  -  -  - 38000 2005
50  -  -  -  - 30000 2003 39000 2004 96400 2005 75600 2006 52200 2007 19600 2008 312800 2008
60  -  -  -  -  -  -  -  - 9600 2005 27600 2006 20400 2007  -  - 57600 2007
90 34000 2001  -  - 48000 2003  -  - 34000 2005  -  -  -  -  -  - 116000 2005
30  -  - 22000 2002 6200 2003  -  - 11400 2005 5200 2006 5000 2007  -  - 49800 2007
 -  -  -  -  -  -  -  -  -  -  -  -  - 14000 2007  -  - 14000 2007
80  -  -  -  -  -  - 107000 2004 200600 2005 118200 2006 84400 2007 98800 2008 609000 2008
10  -  -  -  - 8800 2003  -  -  -  -  -  -  -  -  -  - 8800 2003
40  -  - 13000 2002  -  -  -  -  -  -  -  -  -  -  -  - 13000 2002
100  -  - 42016 2002  -  -  -  - 31800 2005 15600 2006 13800 2007  -  - 103216 2007

Rows 1 - 10. More rows exist.

DEPARTMENT_ID	VAL1_VAL	VAL1_COL	VAL2_VAL	VAL2_COL	VAL3_VAL	VAL3_COL	VAL4_VAL	VAL4_COL	VAL5_VAL	VAL5_COL	VAL6_VAL	VAL6_COL	VAL7_VAL	VAL7_COL	VAL8_VAL	VAL8_COL	TOTAL_VAL	TOTAL_COL
20	-	-	-	-	-	-	26000	2004	12000	2005	-	-	-	-	-	-	38000	2005
50	-	-	-	-	30000	2003	39000	2004	96400	2005	75600	2006	52200	2007	19600	2008	312800	2008
60	-	-	-	-	-	-	-	-	9600	2005	27600	2006	20400	2007	-	-	57600	2007
90	34000	2001	-	-	48000	2003	-	-	34000	2005	-	-	-	-	-	-	116000	2005
30	-	-	22000	2002	6200	2003	-	-	11400	2005	5200	2006	5000	2007	-	-	49800	2007
-	-	-	-	-	-	-	-	-	-	-	-	-	14000	2007	-	-	14000	2007
80	-	-	-	-	-	-	107000	2004	200600	2005	118200	2006	84400	2007	98800	2008	609000	2008
10	-	-	-	-	8800	2003	-	-	-	-	-	-	-	-	-	-	8800	2003
40	-	-	13000	2002	-	-	-	-	-	-	-	-	-	-	-	-	13000	2002
100	-	-	42016	2002	-	-	-	-	31800	2005	15600	2006	13800	2007	-	-	103216	2007

Statement 31

with rws as (  
  select to_char ( order_datetime, 'Mon' ) mnth, 
         store_id,  
         quantity * unit_price total 
  from   co.orders o 
  join   co.order_items 
  using  ( order_id ) 
  where  order_datetime >= timestamp '2018-09-01 00:00:00' 
  and    order_datetime <  timestamp '2018-12-01 00:00:00' 
) 
select * from cross_table_pivot ( 
  table_name => rws,  
  pivot_col  => columns ( mnth ),  
  rows_col   => columns ( store_id ),  
  pivot_val  => columns ( total ),  
  num_pivot_cols => 3 
) 
order  by 1


STORE_ID VAL1_VAL VAL1_COL VAL2_VAL VAL2_COL VAL3_VAL VAL3_COL TOTAL_VAL TOTAL_COL
1 33721.94 Nov 38023.1 Oct 40786.44 Sep 112531.48 Sep
10 2686.8 Nov 757.32 Oct 88 Sep 3532.12 Sep
11 1091.8 Nov 1320.54 Oct 1176.44 Sep 3588.78 Sep
12 1415.82 Nov 272 Oct 944.92 Sep 2632.74 Sep
13 449.5 Nov  -  -  -  - 449.5 Nov
14 1366.14 Nov 302 Oct  -  - 1668.14 Oct
15 1034.2 Nov  -  -  -  - 1034.2 Nov
2 684.84 Nov 2445.08 Oct 647.14 Sep 3777.06 Sep
3 705.08 Nov 854.34 Oct 2269.78 Sep 3829.2 Sep
4 984.04 Nov 1614.86 Oct 354.88 Sep 2953.78 Sep

Rows 1 - 10. More rows exist.

STORE_ID	VAL1_VAL	VAL1_COL	VAL2_VAL	VAL2_COL	VAL3_VAL	VAL3_COL	TOTAL_VAL	TOTAL_COL
1	33721.94	Nov	38023.1	Oct	40786.44	Sep	112531.48	Sep
10	2686.8	Nov	757.32	Oct	88	Sep	3532.12	Sep
11	1091.8	Nov	1320.54	Oct	1176.44	Sep	3588.78	Sep
12	1415.82	Nov	272	Oct	944.92	Sep	2632.74	Sep
13	449.5	Nov	-	-	-	-	449.5	Nov
14	1366.14	Nov	302	Oct	-	-	1668.14	Oct
15	1034.2	Nov	-	-	-	-	1034.2	Nov
2	684.84	Nov	2445.08	Oct	647.14	Sep	3777.06	Sep
3	705.08	Nov	854.34	Oct	2269.78	Sep	3829.2	Sep
4	984.04	Nov	1614.86	Oct	354.88	Sep	2953.78	Sep

Futuristic SQL Making the impossible possible

Additional Information