Your explanation is easy to understand to people who use Excel and SQL on regular basis else pivot concept w.r.t. sql will be hard to understand. It helped me.. Thanks Bro :)
Hello Siva, In my last interview, the interviewer stood me this question and I said to her I never heard anything like a pivot in ORACLE. Now I understood the concept very well with your easy example. I will practice the same and prepare for next time. Thanks a lot. :) Keep posting the good stuffs
@@SivaAcademy sir apki help ki jrurt h sql learning m or mne abhi strt kia h financial condition shi nhi hone ki wjh se m bhar training nhi le skta if u can hlp me msg me on whats app 8950166430
Thankyou Sir , one this is , in the IN clause we give hardcoded values like (10,20,30) so if some new deptno is added we wont get the sum of sal for that dept unless we update the code and we will have to amend the query every-time a new deptno is added cant we do anything like instead of giving hardcoded values if we could pass (select distinct DEPTNO from EMP) something like that !!
Hi, but if you know that columns are incrementing?? I say, a deptno is deleted and probably you added three new deptno's?? you have to change every time the query?? is there any posibility to make it dinamic.
Hi Sir, all your videros are very very helpful to all please keep make some more videos, i will share some more interview question through mail sir thank you so much for keep sharing us your knowledge
Hey Siva, Your playlist says, this is the first video, Please can you help me with a link where you have explained the editor you have used and how to connect excel with the SQL, please.
In this excel, I have not retrieved the data from DB into EXCEL, instead, I used Excel to understand the pivot concept. I will cover the Excel+DB in separate video
@@SivaAcademy Thank you for the response, please may I know about PL SQL editor you have used and recommend. Is that freeware, I can download it. I really look forward for interfacing excel with the database using ADO and if in SharePoint, if excel can refresh data and pull data live from the database with a dynamic dropdown then that would be so cool. As I have subscribed, I look forward to your basics to follow your rest of the videos.
Thank you for your explanation, sir. can you explanation this function (pivot) with dynamic data to accommodate the increasing data??? thank you in advance
1. is it always possible replacing join(equi/left/right etc) with where ? is result will be same? 2.OUTER join cannot be used with IN/OR operator,can you explain this? 3.inner/where predicates --same effect outer/where predicates--different result
Hello siva, you explained with very good example and easy way. I am looking for having a training. Please give me details to contact you or your staff. Appreciate your faster response. Thank you
Here column should be explicitly mentioned in the query. If there is another dept added in the table, we have to change the query. Also, same result can be achieved through decode and union clause.
thank you sir. your video is so much helpful. for Dept No IN condition (at 6 min 55 sec you have final query) instead of hard coding dept no 's can it be possible to take the distinct list of dept no's ? i tried but was giving error. please guide.
I thought Some videos Like these not added in any of your playlist... Add it in a name like SQL and something... And one more question?? Display department wise employees name in column wise ... Not for the name shake .. Really your explanations are very clear .. Please go-ahead and arrange all videos in proper playlists...
Yaa Aravind, There were few videos not part of any playlist, those videos i created initially, thats why i didnt add into any playlist. Sure, I will create and add those missing videos to proper playlist for easy access. Sure, I will add the question. Is this the output what you are looking for? Output 1: DEPNO NAME 10 SCOTT, MARK, BLAK 20 JAMES,SMITH,....... 30 KING,SIVA Output 2: DEPTNO_10 DEPTNO_20 DEPTNO_30 SCOTT JAMES KING MARK SMITH SIVA BLAK. you can also mention the expected output with sample sample data., Thanks again, Regards, Siva
@@SivaAcademy super sir .. Please post queries for both output 1 and 2 .. I have expected output 2 .. I just asked to add this question on your schedule .. Please post on your availability . thanks
@Revanth, Use alias in the inner part, and use NLV function in the outer select clause, SELECT JOB, NVL(DEPT_10,0) DEPT_10, NVL(DEPT_20,0) DEPT_20, NVL(DEPT_30,0) DEPT_30 FROM(SELECT JOB,DEPTNO,SAL FROM EMP) PIVOT ( SUM ( SAL ) FOR DEPTNO IN ( 10 DEPT_10,20 DEPT_20,30 DEPT_30)); JOB, DEPT_10, DEPT_20, DEPT_30 --------------------------------------------------------------- ANALYST 0 6000 0 CLERK 1300 1900 950 SALESMAN 0 0 5600 MANAGER 2450 2975 2850 PRESIDENT 5000 0 0 --------------------------------------------------------------- Thanks, Siva
i want to use multiple select statement in one queiry ?? Like SELECT COUNT(*) HEAD FROM EMP WHERE JOB IN ('PRESIDENT'); union SELECT COUNT(*) MANAGER FROM EMP WHERE JOB IN ('MANAGER'); union SELECT COUNT(*) BASEEMPLOYEES FROM EMP WHERE JOB NOT IN ('PRESIDENT','MANAGER'); Head 1 3 8 but i want result like this HEAD MANGER BASEEMP 1 3 8
Option 1: select (SELECT COUNT(*) HEAD FROM EMP WHERE JOB IN ('PRESIDENT')) HEAD, (SELECT COUNT(*) MANAGER FROM EMP WHERE JOB IN ('MANAGER')) MANGER , (SELECT COUNT(*) BASEEMPLOYEES FROM EMP WHERE JOB NOT IN ('PRESIDENT','MANAGER')) BASEEMP FROM DUAL; Option 2: select count(case when job = 'PRESIDENT' then job end) HEAD, count(case when job = 'MANAGER' then job end) MANAGER, count(case when job not in( 'PRESIDENT','MANAGER') then job end) OTHERS from emp; Option 3: select sum(decode(job,'PRESIDENT',1)) HEAD, sum(decode(job,'MANAGER',1)) MANAGER, sum(decode(job,'MANAGER',0,'PRESIDENT',0,1)) OTHERS from emp;
Very Good explanation...I will never confuse in using pivot. Thanks for sharing your knowledge
@Abhishek, Thanks for your comment :-)
Your explanation is easy to understand to people who use Excel and SQL on regular basis else pivot concept w.r.t. sql will be hard to understand. It helped me.. Thanks Bro :)
Welcome bro 💐💐
Complex pivot understanding became simple sir. Thank you
Super siva simple and clear cut❤
Thank you bro 💐❤️
Nice & very much usefull video. Lot of people will get the clear idea on Pivot in Oracle. Thank you so much
It's my pleasure, thanks for your comments bro 🙏
Great explanation by showing what areas to put y/x axis fields and aggregated data
thank you
Its really a wonderful and simple way to write a pivot …..pivot was a fear for me and you made it very easy to learn sir ☺️
Thanks a lot 🙌
My pleasure bro 💐💐🙏🙏
Your reference to excel, made this tutorial very easy to understand !
Glad it was helpful!
Hello Siva,
In my last interview, the interviewer stood me this question and I said to her I never heard anything like a pivot in ORACLE.
Now I understood the concept very well with your easy example. I will practice the same and prepare for next time.
Thanks a lot. :)
Keep posting the good stuffs
@Jerald Louis, Thanks for your comments :-)
Regards,Siva
@@SivaAcademy sir apki help ki jrurt h sql learning m or mne abhi strt kia h financial condition shi nhi hone ki wjh se m bhar training nhi le skta if u can hlp me msg me on whats app 8950166430
Please send me mail to Siva.k.academy@gmail.com
Excellently explained, pivot is not confusing anymore.
Regards,
Sujaa
Welcome 🙏 thank you
Thankyou Sir ,
one this is , in the IN clause we give hardcoded values like (10,20,30)
so if some new deptno is added we wont get the sum of sal for that dept unless we update the code and we will have to amend the query every-time a new deptno is added
cant we do anything like instead of giving hardcoded values if we could pass (select distinct DEPTNO from EMP) something like that !!
I can see a smile on my face after watching this video.
Your videos are really awesome, Kindly post videos on hints and partition concept
Definitely, please stay tuned
Thanks Siva for another helpful video!!
Welcome bro, thank you
Fantastic video and example. 🙏
Thank you
OMG, Very well explained! Thanks!
Thank you 💐
Just a brilliant way of explaining the tough concept. Thank you
Welcome bro
Hi, but if you know that columns are incrementing?? I say, a deptno is deleted and probably you added three new deptno's?? you have to change every time the query?? is there any posibility to make it dinamic.
Good to hear the knowledge from u.... U are helping to lot of people's..
Welcome bro 🙏
Thanks you for explaining pivot in very simple way 👌🏿
Welcome 🙏
Hi Sir, all your videros are very very helpful to all please keep make some more videos, i will share some more interview question through mail sir thank you so much for keep sharing us your knowledge
Sure, please share your questions, I will post as part of upcoming videos
Hey Siva, Your playlist says, this is the first video, Please can you help me with a link where you have explained the editor you have used and how to connect excel with the SQL, please.
In this excel, I have not retrieved the data from DB into EXCEL, instead, I used Excel to understand the pivot concept. I will cover the Excel+DB in separate video
@@SivaAcademy Thank you for the response, please may I know about PL SQL editor you have used and recommend. Is that freeware, I can download it. I really look forward for interfacing excel with the database using ADO and if in SharePoint, if excel can refresh data and pull data live from the database with a dynamic dropdown then that would be so cool. As I have subscribed, I look forward to your basics to follow your rest of the videos.
very well explanation
Thank you
Superb Explanation
Thank you 🙂
Thank you for your explanation, sir.
can you explanation this function (pivot) with dynamic data to accommodate the increasing data???
thank you in advance
1. is it always possible replacing join(equi/left/right etc) with where ? is result will be same?
2.OUTER join cannot be used with IN/OR operator,can you explain this?
3.inner/where predicates --same effect
outer/where predicates--different result
Hello siva, you explained with very good example and easy way. I am looking for having a training. Please give me details to contact you or your staff.
Appreciate your faster response.
Thank you
Excellent Sir, really appreciate it. Can you please explain Hash Table's & Indexes also....Please
It's my pleasure,Thank you so much :-), Sure, Please stay tuned, i will explain soon
Thank you very much.Its simply superb.
It's my pleasure,Thank you so much :-)
Here column should be explicitly mentioned in the query. If there is another dept added in the table, we have to change the query. Also, same result can be achieved through decode and union clause.
thank you sir. your video is so much helpful. for Dept No IN condition (at 6 min 55 sec you have final query) instead of hard coding dept no 's can it be possible to take the distinct list of dept no's ? i tried but was giving error. please guide.
its possible, but not directly, will post a dynamic pivot soon, please stay tuned.
Thanks Guru
🙏🙏
Hi siva, i need ans
how to generate date between start and end date using by procedure oracle(we should use parameter)
u r the best..
🙏🙏🙏
I thought Some videos Like these not added in any of your playlist...
Add it in a name like SQL and something...
And one more question??
Display department wise employees name in column wise ...
Not for the name shake ..
Really your explanations are very clear ..
Please go-ahead and arrange all videos in proper playlists...
Yaa Aravind,
There were few videos not part of any playlist, those videos i created initially, thats why i didnt add into any playlist.
Sure, I will create and add those missing videos to proper playlist for easy access.
Sure, I will add the question.
Is this the output what you are looking for?
Output 1:
DEPNO NAME
10 SCOTT, MARK, BLAK
20 JAMES,SMITH,.......
30 KING,SIVA
Output 2:
DEPTNO_10 DEPTNO_20 DEPTNO_30
SCOTT JAMES KING
MARK SMITH SIVA
BLAK.
you can also mention the expected output with sample sample data.,
Thanks again,
Regards,
Siva
@@SivaAcademy super sir .. Please post queries for both output 1 and 2 ..
I have expected output 2 ..
I just asked to add this question on your schedule .. Please post on your availability . thanks
Sure Aravind,
I will post a video on the various methods to achieve the result. stay tuned.
Thanks,
Siva
Perfect
Thank you
Hi sir thank you for sharing very useful things and please make a video about partitions concept asap thanks in advanced
Sure 👍 please stay tuned for more videos
@@SivaAcademy thank you sir .I am following your videos every day..God bless you sir
Sir what about grand total column?...!
Can we have functionality of using pivot function without aggregate function rather wanted to have another column value.
Please provide me a sample data to understand better and post back
Good Explanation :)
Thank you
Hi Siva, how to replace null values to 0 when using oracle SQL pivot
Please use nvl function wherever you want to replace null with a default value
I already used nvl function but, still it's blank in the report
Too good
Thank you 💐
How can we select a particular row on Y-axis i.e. jobs like only clerk manager and analyst and rest eliminate in view.
You can filter only the records(in main query using where condition) that you need to display in final output
@@SivaAcademy thanks
Thank you.
Welcome :-)
How to add total sal column in the end for each job ,if deptno on x axis and job in y axis
You can watch this video to see how to compute group totals.
th-cam.com/video/wodH9bKD3qg/w-d-xo.html
Well done
Thank you :-)
Hi. Pleas tell me how can we do in clause dynamic while using pivot. I mean I want '10' as 10, '20' as 20 dynamic not static...
I will explain in upcoming video, please stay tuned
how to do for in string without hard coded
Nice explanation. Please keep it up.
I will try my best
and pivot dynamic???
@ELEAUT, few things can be made dynamic, but not everything, give me an example what you are looking for, i can try and post it back.
How to pivot a row word 'oracle'as column can anyone write syntax
how to exclude null in the result of pivot table
@revant, how about using NVL function?
@@SivaAcademy thanks for the reply, i tried but is not working
@revanth, can you send me the query that you are trying, send me the datasetup script, and expected output.
Thanks,Siva
@@SivaAcademy *
3 FROM
4 (
5 SELECT job,deptno,sal
6 FROM emp
7 )
8 PIVOT ( sum ( sal )
9 FOR deptno
10 IN ( 10,20,30 )
11 );
JOB 10 20 30
--------- ---------- ---------- ----------
SALESMAN 5600
CLERK 1300 1900 950
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
in those empty places(nulls) i want 0
@Revanth, Use alias in the inner part, and use NLV function in the outer select clause,
SELECT JOB, NVL(DEPT_10,0) DEPT_10, NVL(DEPT_20,0) DEPT_20, NVL(DEPT_30,0) DEPT_30
FROM(SELECT JOB,DEPTNO,SAL
FROM EMP)
PIVOT ( SUM ( SAL )
FOR DEPTNO IN ( 10 DEPT_10,20 DEPT_20,30 DEPT_30));
JOB, DEPT_10, DEPT_20, DEPT_30
---------------------------------------------------------------
ANALYST 0 6000 0
CLERK 1300 1900 950
SALESMAN 0 0 5600
MANAGER 2450 2975 2850
PRESIDENT 5000 0 0
---------------------------------------------------------------
Thanks,
Siva
i want to use multiple select statement in one queiry ??
Like
SELECT COUNT(*) HEAD FROM EMP WHERE JOB IN ('PRESIDENT');
union
SELECT COUNT(*) MANAGER FROM EMP WHERE JOB IN ('MANAGER');
union
SELECT COUNT(*) BASEEMPLOYEES FROM EMP WHERE JOB NOT IN ('PRESIDENT','MANAGER');
Head
1
3
8
but i want result like this
HEAD MANGER BASEEMP
1 3 8
Option 1:
select
(SELECT COUNT(*) HEAD FROM EMP WHERE JOB IN ('PRESIDENT')) HEAD,
(SELECT COUNT(*) MANAGER FROM EMP WHERE JOB IN ('MANAGER')) MANGER ,
(SELECT COUNT(*) BASEEMPLOYEES FROM EMP WHERE JOB NOT IN ('PRESIDENT','MANAGER')) BASEEMP
FROM DUAL;
Option 2:
select count(case when job = 'PRESIDENT' then job end) HEAD,
count(case when job = 'MANAGER' then job end) MANAGER,
count(case when job not in( 'PRESIDENT','MANAGER') then job end) OTHERS
from emp;
Option 3:
select sum(decode(job,'PRESIDENT',1)) HEAD,
sum(decode(job,'MANAGER',1)) MANAGER,
sum(decode(job,'MANAGER',0,'PRESIDENT',0,1)) OTHERS
from emp;
Thanks u sir