Oracle Pivot with group total computation

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...

[@MrBandhuraj]

Great KZbin 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.

[@murugesanpitchaikani4808]

Hi, we are hard coded column values in pivot. Is it possible to pass dynamic query in pivot clause? Except xml...

[@Anushkathecuteprincess]

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

[@rahulbadhe5930]

thank you sir ,, its very good session. please take a performance tuning about session , its more needed.

[@SivaAcademy]

@Rahul, Thanks for your comments, Sure will start posting performance related videos soon, Please stay tuned.

[@vijayarjunwadkar]

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...

[@SivaAcademy]

Thank you bro 💐

[@govindaraju3265]

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

[@sujaa1000]

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

[@SivaAcademy]

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

[@sujaa1000]

@@SivaAcademy Thank you Siva

[@sridhartamilselvan]

Your's teaching skill is awesome 👌

[@SivaAcademy]

🙏🙏🙏

[@SivaAcademy]

@sridhar thank you ☺️

[@niyije]

Thank you a lot! it runs in Oracle

[@SivaAcademy]

Welcome 💐🙏👍

[@janf5021]

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.

[@VinayKumar-ij4eu]

Thanks Siva for this video!!

[@SivaAcademy]

Welcome bro

[@mahendra7121]

Can we get same output without using pivot operation ?

[@Hemanthmech]

Excellent teaching thank you

[@SivaAcademy]

Welcome 💐

[@dheenadhayalan2091]

Thanks Very useful, can we use this qry in Oracle report to design same structure ?

[@SivaAcademy]

Thanks bro, I am not sure about Oracle reports

[@sajeedshaik7280]

thanks for posting valuble info..

[@SivaAcademy]

Welcome 🙏👍

[@vinaygajjelli8028]

i have 10g in my lappy which is not usefull to do any pivot clause..it can be done from 11g version

[@ramus6188]

SUB GROUP NOT MENTIONED. CAN WE EXPECT IT

[@karthikeyan9592]

Great 👍

[@surajmokatkar]

Good Explanation.Precise.

[@SivaAcademy]

Thank you

[@Andhrasinchennai]

Excellent explanation

[@SivaAcademy]

Thank you

[@bhushanpalse516]

Sir if there are 3 tables in from clause,then how to use this pivot

[@SivaAcademy]

Pivot is for result set of any query having any number of tables, inner query can have any number of tables

[@md.rahimuddinshohag7202]

Thank you so much

[@SivaAcademy]

Welcome 💐

[@BongFoody]

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.

[@BongFoody]

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.

[@SivaAcademy]

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

[@BongFoody]

Thanks Sir

[@reshmabahadur7564]

Siva, how much will b charged for the complete course and duration??

[@SivaAcademy]

please drop mail to [e-mail]

[@funzone123456]

very good video. pivot explanation simplified rather using traditional max decode logic

[@SivaAcademy]

Welcome 👍💐

[@VethakaliVethuku]

Good Explanation :) Cube ,Rollup

[@SivaAcademy]

Thank you

[@chinarisubudhi8865]

Nicely explained..

[@SivaAcademy]

Thank you so much 🙂

[@vaniganapathi830]

Thank you so much sir

[@SivaAcademy]

Most welcome

[@rk-ej9ep]

Excellent sir...

[@SivaAcademy]

Thank you

[@TamilManivs]

Outstanding teaching skill bro

[@SivaAcademy]

Thank you 💐 bro

[@pranayk2840]

Hello Sir, I'm getting the below error: ORA-01748: only simple column names allowed here

[@SivaAcademy]

can you please send me the query you tried.

[@shashikumarn.s859]

Amazing teaching thanks

[@SivaAcademy]

Welcome 🙏💐

[@boyachi002]

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 ;

[@SivaAcademy]

Pivot is not very flexible in terms of dynamic inputs, however you can explore 18C Polymorphic Table Functions

[@alladivamshikrishna228]

hi sir, can we do cummilative sum to some particular rows in one column.

[@SivaAcademy]

Yes, you can write entire query in inner subquery, and in outer query you compute cumulative sum

[@alladivamshikrishna228]

​@@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.

[@vsr1727]

Thanks Siva

[@tejupansare4947]

U r any institute available in Pune

[@SivaAcademy]

No... Only online

[@neverstopexploring5265]

It is very useful brother

[@SivaAcademy]

Welcome 🙏 bro

[@KrishnaPaneri]

Thank you 👌🏿👍🏿

[@SivaAcademy]

You’re welcome 😊

[@SivaAcademy]

Please look into this video kzbin.info/www/bejne/gavYiHaChdSgmbs

[@pankajkharade6936]

Superb Sir 👍👍👍

[@SivaAcademy]

Welcome bro

[@tharinduhasitha3618]

Thanks

[@SivaAcademy]

Welcome 🙏

[@deepakmeda8049]

Super bro.

[@SivaAcademy]

🙏

[@849ramzes]

Gj Buddy 👍🏻

[@SivaAcademy]

Thank you 🙏

[@reshmabahadur7564]

👍👍

[@SivaAcademy]

Thank you