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

Oracle sql Interview Question : How to change rows to column

93 ratings | 13330 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 (23)
Raja Sekhar (7 months ago)
Hi..Thanks for your video.its very nice. If possible please talk louder.
R Jena (8 months 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...
MRIGENDRA PRATAP SINGH (9 months ago)
Nice....:)
Kishan Mashru (9 months ago)
am glad you enjoyed, hope you have liked the video and subscribed to the channel
Sayan Roy (9 months ago)
How can I store into a table after converting row to col
Kishan Mashru (9 months 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 (10 months 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 (9 months 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 (1 year 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 (1 year 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 (1 year ago)
Is it possible to add a constraint (primary key) in a existing table with out mentioning the constraint name?
Kishan Mashru (1 year 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 (1 year ago)
Thanks for your reply.
Vicky ETL (1 year ago)
yes you can add the constraint to existing table
ipseeta sahu (1 year ago)
what will be the OUTPUT of select count(*) from emp where 1=2 ;
mahesh chowdary (10 months ago)
if count function fails it always retuns 0 only
ipseeta sahu (1 year ago)
Kishan Mashru, Thanks for your reply :)
Kishan Mashru (1 year ago)
WHERE 1 = 2; is an always false condition, meaning the select query would not return anything, so the count(*) will return 0.
Vicky ETL (1 year ago)
Hi kishan , tell the real time example for pivot
Kishan Mashru (1 year 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.