HomeНаука и техникаRelated VideosMore From: Kishan Mashru

Oracle sql Interview Question : How to change rows to column

164 ratings | 21147 views
This video tutorial talks about the frequently asked oracle sql interview question of changing rows to the column. Based on the given scenario, we are asked to change the presentation of data in a table with 3 rows and 2 column to 3 columns and 2 rows. Decode Function in oracle https://youtu.be/YUAjPMjqMws Pivot and unpivot in oracle https://youtu.be/6s0DI1YcWIM
Html code for embedding videos on your blog
Text Comments (34)
LadyChopin (1 month ago)
Thanks a lot! I had tried a few other tutorials but only yours got me to understand how to pivot, thanks :)
Threelly AI (2 months ago)
2019 anyone? https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo
Threelly AI (2 months ago)
My CAT Decided What I ATE for 24 HOURS (And This Is What Happended...) https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo
Eve Ct. (2 months ago)
This message is for the person in the video - is there a way to speak to you live? I am based out of US (Dallas, TX) - I cannot mention the reason here for confidentiality - please reply back to this message so that i can see it’s you and i will contact you
Shahid Aman (3 months ago)
Hi Bro...it's very good Explanation I have a doubt. you have done with numbers right then how can we do if it is a string means
Jerry jose (3 months ago)
how to do it without hardcoding the values?
Preeti Mishra (4 months ago)
Could you please speak a bit louder.Without headphones it's not possible?
m lipina (4 months ago)
Nice explanation ,but there is no volume at all , very difficult to listen without headset ,
Pranjal Salunke (6 months ago)
source: a b c 1 2 3 Target: a 1 b 2 c 3 plz help to solve
Kurugod Prasanna (6 months ago)
Do you have videos on performance tuning? If so, could you please share me the link?
Raja Sekhar (1 year ago)
Hi..Thanks for your video.its very nice. If possible please talk louder.
R Jena (1 year ago)
suppose the col contain the rows like col ------- k i s h a n I want u to make the output like k i s h a n plz help me out...
Kishan Mashru (1 year ago)
am glad you enjoyed, hope you have liked the video and subscribed to the channel
Sayan Roy (1 year ago)
How can I store into a table after converting row to col
Kishan Mashru (1 year ago)
Sayan Roy try writing a insert into target_tbl_name select .... Hope this helps!!! Please subscribe to the channel and like the video.
Dmitry Khalatov (1 year ago)
Not sure it really answers the original question. I can even simplify your query to SELECT 1 as C1, 2 as C2, 3 as C3 from dummy UNION SELECT 4 as C1, 5 as C2, 6 as C3 FROM dummy; - but I think interviewer usually means that your query has to work on any values without being rewritten
Kishan Mashru (1 year ago)
Sure, even they would try and complicate it by asking, "Do you think this is the best approach" :) But who cares, if its not an interview and you simply want the output, here's the way!!! Hope you enjoyed the video and liked it, please subscribe to the channel if you haven't already.
Sameer Ahmad (1 year ago)
Hi Kishan, tell one thing brother if I have a table with a large no of data suppose 20 rows & five columns, then everytime I have to enter the data values ?..like I can enter the e_id for pivot but it becomes difficult & time consuming in entering the name, designation, salary all these thing. can't I use a subquery to extract the data instatntly. I believe yo have understand my doubt. for ex select * from (select e_id from xyz) pivot(sum(e_id) for e_id in (100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120)) union select * from (select e_name from xyz) pivot(sum(e_name) for e_name in (select e_name from xyz where e_id >= 100)); Kindly give me a reply....
Sameer Ahmad (1 year ago)
yes understood, but brother can you plz mail me a simple example of how to convert my this small table's column into rows ,..... e_id e_name depa salary 100 Sameer S 2000 101 Ashok S 2200 102 Affan P 1500 103 Nisha S 1600 104 Raj D 1800 105 Rahul P 1600
Kishan Mashru (1 year ago)
I believe you are talking on the for e_id in (....) part Ya even I had once thought the same, but unfortunately Oracle does not allow any subquery for that in clause, nor does it allow any collection objects, but to reduce the pain what you can do is use the execute immediate (dynamic query generation) you can have the query being built at runtime and then use the query as on. Hope this helps!!!
Nikhil Kulshrestha (2 years ago)
i want to compare the two dates and show the new column as week. suppose there is two week in 1 payroll. 1-row start date and end date 2 row start date and end date just want to add new column as week if 1 row is less then 2 and row then it shows 1 week and onther will be 2 week or vise versa. 9716688635 here is my number if anone can solve please let me know
Kishan Mashru (2 years ago)
The question is a bit confusing brother, not able to get what you exactly want, try and post the question with or as an example!!!
ipseeta sahu (2 years ago)
Is it possible to add a constraint (primary key) in a existing table with out mentioning the constraint name?
Kishan Mashru (2 years ago)
Yup, we can avoid naming the constraint and oracle will give some sys_ name to it. ALTER TABLE table_name ADD PRIMARY KEY (columns,..);
ipseeta sahu (2 years ago)
Thanks for your reply.
ur (2 years ago)
yes you can add the constraint to existing table
ipseeta sahu (2 years ago)
what will be the OUTPUT of select count(*) from emp where 1=2 ;
Samiksha Bhatnagar (1 month ago)
Why have you used sum in front of decode .. any specific reason . How to take only specefic value if like min mix value from row
Mahesh Pothini (1 year ago)
if count function fails it always retuns 0 only
ipseeta sahu (2 years ago)
Kishan Mashru, Thanks for your reply :)
Kishan Mashru (2 years ago)
WHERE 1 = 2; is an always false condition, meaning the select query would not return anything, so the count(*) will return 0.
ur (2 years ago)
Hi kishan , tell the real time example for pivot
Kishan Mashru (2 years ago)
check the link https://youtu.be/6s0DI1YcWIM you can have scenario like for the table employees having columns emp_id, emp_name, dept_id. get the count of employees in every department, in the following format dept 10 dept20 dept30 ------------------------------------------------------ 5 18 10

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.