Great RUclips lesson. I learned a lot about pivot and cube. Question: if I have 2 aggregate sum() and count() in pivot the output is with 2 columns for each department displaying horizontally, How would you display sum() and count() in 2 different rows under a department column for each Job.
Really very helpful.. but i want to implement it into dynamic columns, i have implemented the dynamic columns query but i want to add this logic on it. and one more i want to add other columns as well, how we can add other columns in group by
Nice video teaching use of Pivot in Oracle SQL. I was struggling with this a bit, but you clarified it in a detailed and smooth way! Thanks a lot! 😊👍 Watching your other videos too...
Oracle is unable to truely pivot dynamically. it works if you declare the column manually in the pivot statement, it does NOT work if you declare it with an inner select statement. The recommendation ist to go via XML, but this will NOT Produce a table column, it creates a XML Tag with a Single Column of the table. Then you can click a setting that PL/SQL or other INTERPRETE XML like a table - but it still is not.
Siva, How is 'TOTAL' itself taken as a deptno, can you please explain that? The inner query has deptno, job and total_sal computed, but 'TOTAL' in "deptno in (10,20,30,'TOTAL') is confusing, where is 'TOTAL' computed? Regards, Sujaa
In the innser query where i have used the nvl(job, 'TOTAL') and nvl(deptno, 'TOTAL'), this will be used in the outer query to compute the overall total value
Sir, I didnt understand why you wrote sum(sal) in the pivot section. As we are already having all the totals using the cube clause, so what the pivot sum(sal) is doing.
I think i found the answer - the sum(sal) in the pivot section is just giving the sum group by job from the inner query, but as the innerquery has all the unique permutation/combination- thus providing the same reault as transposed in the final output. Sir, Please correct in case i an having wrong understanding.
You are right, anyway we are having unique combination in inner query, outer sum is not needed, but we added just for the pivot syntax, otherwise pivot won't work
How do i use pivot function by dynamic condition like below source ? pivot (sum(TiresShift) for BuildingStartShift in ( select to_char(sysdate,'YYYYMMDD')||1 "NOW1",to_char(sysdate,'YYYYMMDD')||2 "NOW2",to_char(sysdate,'YYYYMMDD')||3 "NOW3" from dual )) order by 2,1 ;
Hi, we are hard coded column values in pivot. Is it possible to pass dynamic query in pivot clause? Except xml...
Great RUclips lesson. I learned a lot about pivot and cube. Question: if I have 2 aggregate sum() and count() in pivot the output is with 2 columns for each department displaying horizontally, How would you display sum() and count() in 2 different rows under a department column for each Job.
Really very helpful.. but i want to implement it into dynamic columns, i have implemented the dynamic columns query but i want to add this logic on it. and one more i want to add other columns as well, how we can add other columns in group by
Thank you a lot! it runs in Oracle
Welcome 💐🙏👍
Nice video teaching use of Pivot in Oracle SQL. I was struggling with this a bit, but you clarified it in a detailed and smooth way! Thanks a lot! 😊👍 Watching your other videos too...
Thank you bro 💐
Your's teaching skill is awesome 👌
🙏🙏🙏
@sridhar thank you ☺️
Excellent teaching thank you
Welcome 💐
Oracle is unable to truely pivot dynamically. it works if you declare the column manually in the pivot statement, it does NOT work if you declare it with an inner select statement.
The recommendation ist to go via XML, but this will NOT Produce a table column, it creates a XML Tag with a Single Column of the table.
Then you can click a setting that PL/SQL or other INTERPRETE XML like a table - but it still is not.
thank you sir ,, its very good session.
please take a performance tuning about session , its more needed.
@Rahul, Thanks for your comments, Sure will start posting performance related videos soon, Please stay tuned.
Siva,
How is 'TOTAL' itself taken as a deptno, can you please explain that?
The inner query has deptno, job and total_sal computed, but 'TOTAL' in "deptno in (10,20,30,'TOTAL') is confusing, where is 'TOTAL' computed?
Regards,
Sujaa
In the innser query where i have used the nvl(job, 'TOTAL') and nvl(deptno, 'TOTAL'), this will be used in the outer query to compute the overall total value
@@SivaAcademy Thank you Siva
Thanks Siva for this video!!
Welcome bro
thanks for posting valuble info..
Welcome 🙏👍
Good Explanation.Precise.
Thank you
Hi sir, well explained with a simple method. Thanks,I have a question like how to get row% and column% as we do in excel
Excellent explanation
Thank you
Good Explanation :) Cube ,Rollup
Thank you
i have 10g in my lappy which is not usefull to do any pivot clause..it can be done from 11g version
Can we get same output without using pivot operation ?
Great 👍
Thank you so much
Welcome 💐
Outstanding teaching skill bro
Thank you 💐 bro
SUB GROUP NOT MENTIONED. CAN WE EXPECT IT
Nicely explained..
Thank you so much 🙂
Amazing teaching thanks
Welcome 🙏💐
Excellent sir...
Thank you
very good video. pivot explanation simplified rather using traditional max decode logic
Welcome 👍💐
Sir if there are 3 tables in from clause,then how to use this pivot
Pivot is for result set of any query having any number of tables, inner query can have any number of tables
Thank you so much sir
Most welcome
Thanks Very useful, can we use this qry in Oracle report to design same structure ?
Thanks bro, I am not sure about Oracle reports
Sir, I didnt understand why you wrote sum(sal) in the pivot section. As we are already having all the totals using the cube clause, so what the pivot sum(sal) is doing.
I think i found the answer - the sum(sal) in the pivot section is just giving the sum group by job from the inner query, but as the innerquery has all the unique permutation/combination- thus providing the same reault as transposed in the final output. Sir, Please correct in case i an having wrong understanding.
You are right, anyway we are having unique combination in inner query, outer sum is not needed, but we added just for the pivot syntax, otherwise pivot won't work
Thanks Sir
Hello Sir, I'm getting the below error:
ORA-01748: only simple column names allowed here
can you please send me the query you tried.
It is very useful brother
Welcome 🙏 bro
Thanks Siva
How do i use pivot function by dynamic condition like below source ?
pivot (sum(TiresShift) for BuildingStartShift in
(
select to_char(sysdate,'YYYYMMDD')||1 "NOW1",to_char(sysdate,'YYYYMMDD')||2 "NOW2",to_char(sysdate,'YYYYMMDD')||3 "NOW3"
from dual
))
order by 2,1
;
Pivot is not very flexible in terms of dynamic inputs, however you can explore 18C Polymorphic Table Functions
Superb Sir 👍👍👍
Welcome bro
Thank you 👌🏿👍🏿
You’re welcome 😊
Please look into this video
ruclips.net/video/KzuWCLPomcY/видео.html
Siva, how much will b charged for the complete course and duration??
please drop mail to siva.k.academy@gmail.com
Super bro.
🙏
Thanks
Welcome 🙏
hi sir, can we do cummilative sum to some particular rows in one column.
Yes, you can write entire query in inner subquery, and in outer query you compute cumulative sum
@@SivaAcademy
emp_id emp_sal cummilative sum
----------- ------------ ---------------
------------
1 10 ------------------- 10 (as it is)
2 35 ------------------- 45 (cummilative sum.i.e, 35+10=45)
3 55 ------------------- 55 (as it is)
4 40 ------------------- 40 (as it is)
5 35 ------------------- 75 (cummilative sum.i.e, 35+40=75)
6 85 ------------------- 85 (as it is)
7 25 ------------------- 25 (as it is)
8 65 ------------------- 90 (cummilative sum.i.e, 65+25=90)
9 45 ------------------- 45 (as it is)
10 65 ------------------- 65 (as it is)
This is requirement sir, is this possible? if it is possible, can i have query for that.
Gj Buddy 👍🏻
Thank you 🙏
U r any institute available in Pune
No... Only online
👍👍
Thank you