Oracle Pivot with group total computation
Vložit
- čas přidán 5. 09. 2024
- Oracle Pivot with group total computation
Watch this video for PIVOT clause
• Oracle Pivot clause | ...
Click here to subscribe to my youtube channel / @sivaacademy
Oracle Interview questions and Answers at • oracle interview quest...
Oracle 18C New features at • Oracle 18C New Feature
Oracle 12C New features at • Oracle 12C New Feature...
Oracle PLSQL records and collections at • oracle plsql records a...
Hi, we are hard coded column values in pivot. Is it possible to pass dynamic query in pivot clause? Except xml...
Great CZcams 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.
Your's teaching skill is awesome 👌
🙏🙏🙏
@sridhar thank you ☺️
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 💐
Good Explanation :) Cube ,Rollup
Thank you
Excellent teaching thank you
Welcome 💐
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.
Excellent explanation
Thank you
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 💐🙏👍
thanks for posting valuble info..
Welcome 🙏👍
Great 👍
Thanks Siva for this video!!
Welcome bro
Good Explanation.Precise.
Thank you
Excellent sir...
Thank you
Nicely explained..
Thank you so much 🙂
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
Thank you so much sir
Most 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.
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
Outstanding teaching skill bro
Thank you 💐 bro
very good video. pivot explanation simplified rather using traditional max decode logic
Welcome 👍💐
Thank you so much
Welcome 💐
Amazing teaching thanks
Welcome 🙏💐
i have 10g in my lappy which is not usefull to do any pivot clause..it can be done from 11g version
Thanks Siva
SUB GROUP NOT MENTIONED. CAN WE EXPECT IT
It is very useful brother
Welcome 🙏 bro
Superb Sir 👍👍👍
Welcome bro
Can we get same output without using pivot operation ?
Thank you 👌🏿👍🏿
You’re welcome 😊
Please look into this video
czcams.com/video/KzuWCLPomcY/video.html
Thanks
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
Thanks Very useful, can we use this qry in Oracle report to design same structure ?
Thanks bro, I am not sure about Oracle reports
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
Super bro.
🙏
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
Gj Buddy 👍🏻
Thank you 🙏
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.
Siva, how much will b charged for the complete course and duration??
please drop mail to siva.k.academy@gmail.com
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.
👍👍
Thank you
U r any institute available in Pune
No... Only online