Тёмный
No video :(

Oracle Pivot clause | How to use PIVOT in SQL 

Siva Academy
Подписаться 47 тыс.
Просмотров 64 тыс.
50% 1

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

Опубликовано:

 

21 авг 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 107   
@AbhishekJaiswalatwork
@AbhishekJaiswalatwork 5 лет назад
Very Good explanation...I will never confuse in using pivot. Thanks for sharing your knowledge
@SivaAcademy
@SivaAcademy 5 лет назад
@Abhishek, Thanks for your comment :-)
@rapursambasivaiah2372
@rapursambasivaiah2372 Месяц назад
Super siva simple and clear cut❤
@SivaAcademy
@SivaAcademy Месяц назад
Thank you bro 💐❤️
@santoshdevadula5071
@santoshdevadula5071 3 года назад
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 :)
@SivaAcademy
@SivaAcademy 3 года назад
Welcome bro 💐💐
@venkatasubrahmanyam9769
@venkatasubrahmanyam9769 5 лет назад
Nice & very much usefull video. Lot of people will get the clear idea on Pivot in Oracle. Thank you so much
@SivaAcademy
@SivaAcademy 5 лет назад
It's my pleasure, thanks for your comments bro 🙏
@rameshbhavanasi3701
@rameshbhavanasi3701 2 года назад
Complex pivot understanding became simple sir. Thank you
@shaylinpadayachee7766
@shaylinpadayachee7766 3 года назад
Great explanation by showing what areas to put y/x axis fields and aggregated data
@SivaAcademy
@SivaAcademy 3 года назад
thank you
@akp9487
@akp9487 3 года назад
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 🙌
@SivaAcademy
@SivaAcademy 3 года назад
My pleasure bro 💐💐🙏🙏
@alihabib7712
@alihabib7712 3 года назад
Your reference to excel, made this tutorial very easy to understand !
@SivaAcademy
@SivaAcademy 3 года назад
Glad it was helpful!
@sujaa1000
@sujaa1000 4 года назад
Excellently explained, pivot is not confusing anymore. Regards, Sujaa
@SivaAcademy
@SivaAcademy 4 года назад
Welcome 🙏 thank you
@anandmuthalageri9480
@anandmuthalageri9480 Год назад
I can see a smile on my face after watching this video.
@yatra2heart
@yatra2heart 5 лет назад
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
@SivaAcademy 5 лет назад
@Jerald Louis, Thanks for your comments :-) Regards,Siva
@vishalsaxena5081
@vishalsaxena5081 3 года назад
@@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
@SivaAcademy
@SivaAcademy 3 года назад
Please send me mail to Siva.k.academy@gmail.com
@DK-go5se
@DK-go5se 5 лет назад
Good to hear the knowledge from u.... U are helping to lot of people's..
@SivaAcademy
@SivaAcademy 5 лет назад
Welcome bro 🙏
@youtubguna77
@youtubguna77 2 года назад
Just a brilliant way of explaining the tough concept. Thank you
@SivaAcademy
@SivaAcademy 2 года назад
Welcome bro
@SureshKumar360
@SureshKumar360 Год назад
OMG, Very well explained! Thanks!
@SivaAcademy
@SivaAcademy Год назад
Thank you 💐
@AlaganiHIMABINDU-yx3wb
@AlaganiHIMABINDU-yx3wb Год назад
Your videos are really awesome, Kindly post videos on hints and partition concept
@SivaAcademy
@SivaAcademy Год назад
Definitely, please stay tuned
@gauravdutta5486
@gauravdutta5486 3 года назад
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 !!
@KrishnaPaneri
@KrishnaPaneri 4 года назад
Thanks you for explaining pivot in very simple way 👌🏿
@SivaAcademy
@SivaAcademy 4 года назад
Welcome 🙏
@Jcvc20070207
@Jcvc20070207 3 года назад
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.
@VinayKumar-ij4eu
@VinayKumar-ij4eu 2 года назад
Thanks Siva for another helpful video!!
@SivaAcademy
@SivaAcademy 2 года назад
Welcome bro, thank you
@MultiSapMan
@MultiSapMan Год назад
Fantastic video and example. 🙏
@SivaAcademy
@SivaAcademy Год назад
Thank you
@devanaidu9406
@devanaidu9406 4 года назад
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
@SivaAcademy
@SivaAcademy 4 года назад
Sure, please share your questions, I will post as part of upcoming videos
@bhushandarade8995
@bhushandarade8995 3 года назад
very well explanation
@SivaAcademy
@SivaAcademy 3 года назад
Thank you
@sheebaashraf
@sheebaashraf 4 года назад
Thank you very much.Its simply superb.
@SivaAcademy
@SivaAcademy 4 года назад
It's my pleasure,Thank you so much :-)
@kkmm3785
@kkmm3785 3 года назад
Superb Explanation
@SivaAcademy
@SivaAcademy 3 года назад
Thank you 🙂
@mrinmoypal1374
@mrinmoypal1374 2 года назад
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.
@Rajahmundry-timepass
@Rajahmundry-timepass 4 года назад
u r the best..
@SivaAcademy
@SivaAcademy 4 года назад
🙏🙏🙏
@sameerahmad7592
@sameerahmad7592 4 года назад
Excellent Sir, really appreciate it. Can you please explain Hash Table's & Indexes also....Please
@SivaAcademy
@SivaAcademy 4 года назад
It's my pleasure,Thank you so much :-), Sure, Please stay tuned, i will explain soon
@FauziAzisVideos
@FauziAzisVideos 2 года назад
Thank you for your explanation, sir. can you explanation this function (pivot) with dynamic data to accommodate the increasing data??? thank you in advance
@oraclelearning4424
@oraclelearning4424 4 года назад
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
@osahoo07
@osahoo07 2 года назад
Thanks Guru
@SivaAcademy
@SivaAcademy 2 года назад
🙏🙏
@techfoundation6905
@techfoundation6905 Месяц назад
Perfect
@SivaAcademy
@SivaAcademy Месяц назад
Thank you
@Rajahmundry-timepass
@Rajahmundry-timepass 4 года назад
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
@aravindrajendran581
@aravindrajendran581 5 лет назад
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...
@SivaAcademy
@SivaAcademy 5 лет назад
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
@aravindrajendran581
@aravindrajendran581 5 лет назад
@@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
@SivaAcademy
@SivaAcademy 5 лет назад
Sure Aravind, I will post a video on the various methods to achieve the result. stay tuned. Thanks, Siva
@farhashaikh3456
@farhashaikh3456 3 года назад
Too good
@SivaAcademy
@SivaAcademy 3 года назад
Thank you 💐
@devanaidu9406
@devanaidu9406 5 лет назад
Hi sir thank you for sharing very useful things and please make a video about partitions concept asap thanks in advanced
@SivaAcademy
@SivaAcademy 4 года назад
Sure 👍 please stay tuned for more videos
@devanaidu9406
@devanaidu9406 4 года назад
@@SivaAcademy thank you sir .I am following your videos every day..God bless you sir
@VethakaliVethuku
@VethakaliVethuku 3 года назад
Good Explanation :)
@SivaAcademy
@SivaAcademy 3 года назад
Thank you
@MohanPalaniappan-e6c
@MohanPalaniappan-e6c Месяц назад
Hi siva, i need ans how to generate date between start and end date using by procedure oracle(we should use parameter)
@mrchetta
@mrchetta 4 года назад
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.
@SivaAcademy
@SivaAcademy 4 года назад
its possible, but not directly, will post a dynamic pivot soon, please stay tuned.
@WorldEye88
@WorldEye88 4 года назад
Thank you.
@SivaAcademy
@SivaAcademy 4 года назад
Welcome :-)
@granand
@granand 3 года назад
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.
@SivaAcademy
@SivaAcademy 3 года назад
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
@granand
@granand 3 года назад
@@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.
@benjaminthomas1406
@benjaminthomas1406 5 лет назад
Well done
@SivaAcademy
@SivaAcademy 5 лет назад
Thank you :-)
@anchalsachdeva1469
@anchalsachdeva1469 2 года назад
Can we have functionality of using pivot function without aggregate function rather wanted to have another column value.
@SivaAcademy
@SivaAcademy 2 года назад
Please provide me a sample data to understand better and post back
@pranabkumartripathy1400
@pranabkumartripathy1400 3 месяца назад
Sir what about grand total column?...!
@junkingjunking8477
@junkingjunking8477 4 года назад
Nice explanation. Please keep it up.
@SivaAcademy
@SivaAcademy 4 года назад
I will try my best
@Pushpendrasingh-rt9eh
@Pushpendrasingh-rt9eh 4 года назад
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...
@SivaAcademy
@SivaAcademy 4 года назад
I will explain in upcoming video, please stay tuned
@user-cf7vc4ki5z
@user-cf7vc4ki5z 8 месяцев назад
how to do for in string without hard coded
@feyie
@feyie Год назад
Hi Siva, how to replace null values to 0 when using oracle SQL pivot
@SivaAcademy
@SivaAcademy Год назад
Please use nvl function wherever you want to replace null with a default value
@feyie
@feyie Год назад
I already used nvl function but, still it's blank in the report
@shubhamnagure7654
@shubhamnagure7654 4 года назад
How can we select a particular row on Y-axis i.e. jobs like only clerk manager and analyst and rest eliminate in view.
@SivaAcademy
@SivaAcademy 4 года назад
You can filter only the records(in main query using where condition) that you need to display in final output
@shubhamnagure7654
@shubhamnagure7654 4 года назад
@@SivaAcademy thanks
@vishwas22khare
@vishwas22khare 4 года назад
How to add total sal column in the end for each job ,if deptno on x axis and job in y axis
@SivaAcademy
@SivaAcademy 4 года назад
You can watch this video to see how to compute group totals. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wodH9bKD3qg.html
@gashok5231
@gashok5231 Год назад
How to pivot a row word 'oracle'as column can anyone write syntax
@eleaut2667
@eleaut2667 5 лет назад
and pivot dynamic???
@SivaAcademy
@SivaAcademy 5 лет назад
@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.
@revanthgunda
@revanthgunda 5 лет назад
how to exclude null in the result of pivot table
@SivaAcademy
@SivaAcademy 5 лет назад
@revant, how about using NVL function?
@revanthgunda
@revanthgunda 5 лет назад
@@SivaAcademy thanks for the reply, i tried but is not working
@SivaAcademy
@SivaAcademy 5 лет назад
@revanth, can you send me the query that you are trying, send me the datasetup script, and expected output. Thanks,Siva
@revanthgunda
@revanthgunda 5 лет назад
@@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
@SivaAcademy
@SivaAcademy 5 лет назад
@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
@vishalsaxena5081
@vishalsaxena5081 3 года назад
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
@SivaAcademy
@SivaAcademy 3 года назад
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;
@vishalsaxena5081
@vishalsaxena5081 3 года назад
Thanks u sir
Далее
Oracle UNPIVOT
4:11
Просмотров 21 тыс.
Oracle Pivot with group total computation
13:41
Просмотров 23 тыс.
🔥НОВАЯ БАТАРЕЯ?😮
00:40
Просмотров 253 тыс.
I'll do it for you!
00:37
Просмотров 2,6 млн
Oracle SQL - PIVOT Clause
18:53
Просмотров 12 тыс.
Solving SQL Query | Rows to Column in SQL
1:13:11
Просмотров 167 тыс.
How To Load One BILLION Rows into an SQL Database
12:17
PIVOT IN SQL | Advanced SQL | Ashutosh Kumar
18:37
Просмотров 8 тыс.
🔥НОВАЯ БАТАРЕЯ?😮
00:40
Просмотров 253 тыс.