Oracle Pivot clause | How to use PIVOT in SQL
HTML-код
- Опубликовано: 19 окт 2024
- Oracle Pivot clause | How to use PIVOT in SQL
easy-learning-t...
--------------------------------------------------------------
This channel is for learning Oracle SQL, PLSQL, DATABASE concepts, MYSQL, ETL, Mongo DB, Python,UNIX and related technologies.
/ sivaacademy
Home page: sivakacademy.b...
Telegram Channel : t.me/SivaAcademy
--------------------------------------------------------------
Home page: sivakacademy.b...
List of all oracle SQL/PLSQL Interview questions sivakacademy.b...
List of all PLSQL Scenario based Interview Questions sivakacademy.b...
List of all Subscriber questions and answers sivakacademy.b...
--------------------------------------------------------------
Oracle Interview questions playlist • oracle interview quest...
Oracle 18C New features playlist • Oracle 18C New Feature
Oracle 12C New features playlist • Oracle 12C New Feature...
Oracle PLSQL records and collections playlist • oracle plsql records a...
--------------------------------------------------------------
My video courses at UDEMY www.udemy.com/...
--------------------------------------------------------------
About Myself:
----------------------
I am Siva, [LEARN | CODE | TRAIN | SHARE].
Being in IT industry for more than 12+ years.
In my day to day job, I work with database technologies including Oracle, Java, Python, MongoDB, talend and UNIX.
I am passionate about "DATA", coding & training.
In my spare time, I teach database technologies , ETL etc.
I am very glad that you are reading my Profile, and I value your time as well as mine.
Looking forward to see you in my videos
For any questions, please drop me a mail at siva.k.academy@gmail.com
--------------------------------------------------------------
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 💐💐
Super siva simple and clear cut❤
Thank you bro 💐❤️
Complex pivot understanding became simple sir. Thank you
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
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 🙏
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.
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!
Excellently explained, pivot is not confusing anymore.
Regards,
Sujaa
Welcome 🙏 thank you
I can see a smile on my face after watching this video.
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 !!
Thanks Siva for another helpful video!!
Welcome bro, thank you
OMG, Very well explained! Thanks!
Thank you 💐
Fantastic video and example. 🙏
Thank you
Just a brilliant way of explaining the tough concept. Thank you
Welcome bro
Good to hear the knowledge from u.... U are helping to lot of people's..
Welcome bro 🙏
Hi siva, i need ans
how to generate date between start and end date using by procedure oracle(we should use parameter)
Thanks you for explaining pivot in very simple way 👌🏿
Welcome 🙏
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.
Your videos are really awesome, Kindly post videos on hints and partition concept
Definitely, please stay tuned
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
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
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
Thank you very much.Its simply superb.
It's my pleasure,Thank you so much :-)
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
Thank you for your explanation, sir.
can you explanation this function (pivot) with dynamic data to accommodate the increasing data???
thank you in advance
Superb Explanation
Thank you 🙂
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.
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
very well explanation
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
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
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
Sir what about grand total column?...!
u r the best..
🙏🙏🙏
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.
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
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.
ruclips.net/video/wodH9bKD3qg/видео.html
Good Explanation :)
Thank you
how to do for in string without hard coded
Nice explanation. Please keep it up.
I will try my best
Thanks Guru
🙏🙏
Perfect
Thank you
How to pivot a row word 'oracle'as column can anyone write syntax
Well done
Thank you :-)
Too good
Thank you 💐
Thank you.
Welcome :-)
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
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.
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