tag:blogger.com,1999:blog-8873109125023142810.post6425911094725123676..comments2024-03-28T16:58:51.302+05:30Comments on Let's Develop in Oracle: SQL Puzzle - Transpose Rows and Shift Values among columns Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-8873109125023142810.post-5977716139133682812018-09-22T17:55:49.472+05:302018-09-22T17:55:49.472+05:30yes i got thatyes i got thatNimish Garghttps://www.blogger.com/profile/15795821721808548808noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-62930546103270509752018-09-22T17:53:22.922+05:302018-09-22T17:53:22.922+05:30Hi Nimish, Please see below query that solves the ...Hi Nimish, Please see below query that solves the puzzle. Do apprise if that the correct pattern of data required. <br />insert into PRODUCT_STAFF_NEW<br />select PRODUCT,<br />max(case when hierarchy_type = 'PRODUCTION' then (case when level1_erole = 'GM' then level1_ecode end) end) as production_gm_ecode ,<br />max(case when hierarchy_type = 'PRODUCTION' then (case when level1_erole = 'GM' then level1_ename end) end) as production_gm_ename,<br />max(case when hierarchy_type = 'PRODUCTION' then (case when level2_erole = 'RM' then level2_ecode<br /> when level1_erole = 'RM' then level1_ecode end) end) as production_rm_ecode, <br />max(case when hierarchy_type = 'PRODUCTION' then (case when level2_erole = 'RM' then level2_ename<br /> when level1_erole = 'RM' then level1_ename end) end) as production_rm_ename,<br />max(case when hierarchy_type = 'PRODUCTION' then (case when level3_erole = 'MGR' then level3_ecode<br /> when level2_erole = 'MGR' then level2_ecode<br /> when level1_erole = 'MGR' then level1_ecode end) end) as production_mgr_ecode, <br />max(case when hierarchy_type = 'PRODUCTION' then (case when level3_erole = 'MGR' then level3_ename<br /> when level2_erole = 'MGR' then level2_ename<br /> when level1_erole = 'MGR' then level1_ename end) end) as production_mgr_ename,<br />max(case when hierarchy_type = 'PRODUCTION' then (case when level4_erole = 'EXEC' then level4_ecode<br /> when level3_erole = 'EXEC' then level3_ecode<br /> when level2_erole = 'EXEC' then level2_ecode<br /> when level1_erole = 'EXEC' then level1_ecode end) end) as production_exec_ecode, <br />max(case when hierarchy_type = 'PRODUCTION' then (case when level4_erole = 'EXEC' then level4_ename<br /> when level3_erole = 'EXEC' then level3_ename<br /> when level2_erole = 'EXEC' then level2_ename<br /> when level1_erole = 'EXEC' then level1_ename end) end ) as production_exec_ename,<br /><br />Not able to post complete query, but posted for Production part, can be replicated for Same and supportTanvi Gargnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-72831846541650531632018-09-21T15:08:05.374+05:302018-09-21T15:08:05.374+05:30Hi Nimish, Please see below query that solves the ...Hi Nimish, Please see below query that solves the puzzle. Do apprise if that the correct pattern of data required<br /><br />insert into PRODUCT_STAFF_NEW<br /><br />select PRODUCT,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level1_erole = 'GM' then level1_ecode end) end) as production_gm_ecode ,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level1_erole = 'GM' then level1_ename end) end) as production_gm_ename,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level2_erole = 'RM' then level2_ecode<br /><br />when level1_erole = 'RM' then level1_ecode end) end) as production_rm_ecode,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level2_erole = 'RM' then level2_ename<br /><br />when level1_erole = 'RM' then level1_ename end) end) as production_rm_ename,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level3_erole = 'MGR' then level3_ecode<br /><br />when level2_erole = 'MGR' then level2_ecode<br /><br />when level1_erole = 'MGR' then level1_ecode end) end) as production_mgr_ecode,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level3_erole = 'MGR' then level3_ename<br /><br />when level2_erole = 'MGR' then level2_ename<br /><br />when level1_erole = 'MGR' then level1_ename end) end) as production_mgr_ename,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level4_erole = 'EXEC' then level4_ecode<br /><br />when level3_erole = 'EXEC' then level3_ecode<br /><br />when level2_erole = 'EXEC' then level2_ecode<br /><br />when level1_erole = 'EXEC' then level1_ecode end) end) as production_exec_ecode,<br /><br />max(case when hierarchy_type = 'PRODUCTION' then (case when level4_erole = 'EXEC' then level4_ename<br /><br />when level3_erole = 'EXEC' then level3_ename<br /><br />when level2_erole = 'EXEC' then level2_ename<br /><br />when level1_erole = 'EXEC' then level1_ename end) end ) as production_exec_ename<br />-- same for SALE and SUPPORT<br />from PRODUCT_STAFF_HIERARCHY group by PRODUCT;Anonymoushttps://www.blogger.com/profile/17182550953284423164noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-43201688399059939742018-09-16T10:25:18.654+05:302018-09-16T10:25:18.654+05:30Hi Nimish,
i have sent the puzzle query on LinkedI...Hi Nimish,<br />i have sent the puzzle query on LinkedIn messenger, kindly review once.<br /><br />Regards,<br />Mohit Mohit https://www.blogger.com/profile/00328290669390820817noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-90069104366163854902018-09-16T05:16:52.639+05:302018-09-16T05:16:52.639+05:30This comment has been removed by the author.Mohit https://www.blogger.com/profile/00328290669390820817noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-38750685441741189342018-09-11T23:06:38.277+05:302018-09-11T23:06:38.277+05:30Thank you for sharing the data issue, I have added...Thank you for sharing the data issue, I have added 3 more products to remove the duplicates.Nimish Garghttps://www.blogger.com/profile/15795821721808548808noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-47011153882208061932018-09-11T20:39:25.249+05:302018-09-11T20:39:25.249+05:30Hi Nimish,
As you mentioned in the post , per pro...Hi Nimish,<br /><br />As you mentioned in the post , per product there will be one row in the output ,so it means there will be three rows , and each product will be having one gm,rm,mgr,exec but in the data set multiple RM or MRG is present for example if query :-<br /><br />select * from PRODUCT_STAFF_HIERARCHY where<br />product='LAPTOP' and HIERARCHY_TYPE='PRODUCTION';<br /><br />you will get the below output :-<br />LAPTOP PRODUCTION 11 L11 RM EXEC<br />LAPTOP PRODUCTION 35 L35 GM RM<br /><br />so there are two RM for the one product.. So in that case one row for each product cannot be possible..<br /><br />Please provide your inputs if i have misunderstood something.Mohit https://www.blogger.com/profile/00328290669390820817noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-52663713305255925752018-09-11T12:05:35.464+05:302018-09-11T12:05:35.464+05:30sir, can i get the sql which you have used to solv...sir, can i get the sql which you have used to solve this problem?Atul Sharmanoreply@blogger.com