rajprakashraj Posted May 6, 2021 Report Share Posted May 6, 2021 I have table with below data: id value type 1 ABC P 1 EFG J 2 ABC J 2 EFG P 3 ABD P 3 KFG J 4 ABD J 4 KFG P 5 NKP P 5 KDP J 6 ABC P 6 EFG J I want to group rows where values are same but flipped the type. i want have ID-1,2,6 grouped as 1 identifier and 3,4 as 1 identifier and 5 as different identifier. Below is expected result: id value type identifier 1 ABC P 1 1 EFG J 1 2 ABC J 1 2 EFG P 1 3 ABD P 2 3 KFG J 2 4 ABD J 2 4 KFG P 2 5 NKP P 3 5 KDP J 3 6 ABC P 1 6 EFG J 1 Identifier can be anything, need not 1,2,3...it can be a,b,c too, just need have unique identifier for the data set combination Quote Link to comment Share on other sites More sharing options...
teluguking Posted May 6, 2021 Report Share Posted May 6, 2021 How do you know, that ABC, EFG are in one group (In this scenario Group1) and ABD, KFG are in another group(In this scenarios Group2) The query has to be built based on the above logic Quote Link to comment Share on other sites More sharing options...
duryaodhana Posted May 6, 2021 Report Share Posted May 6, 2021 If all the data you have is only the above set of records, then use a CASE statement. Quote Link to comment Share on other sites More sharing options...
zarathustra Posted May 6, 2021 Report Share Posted May 6, 2021 6 hours ago, rajprakashraj said: I have table with below data: id value type 1 ABC P 1 EFG J 2 ABC J 2 EFG P 3 ABD P 3 KFG J 4 ABD J 4 KFG P 5 NKP P 5 KDP J 6 ABC P 6 EFG J I want to group rows where values are same but flipped the type. i want have ID-1,2,6 grouped as 1 identifier and 3,4 as 1 identifier and 5 as different identifier. Below is expected result: id value type identifier 1 ABC P 1 1 EFG J 1 2 ABC J 1 2 EFG P 1 3 ABD P 2 3 KFG J 2 4 ABD J 2 4 KFG P 2 5 NKP P 3 5 KDP J 3 6 ABC P 1 6 EFG J 1 Identifier can be anything, need not 1,2,3...it can be a,b,c too, just need have unique identifier for the data set combination If you keep the values the same but flip the type wouldn't it look like this: id value type 1 ABC J 1 EFG P 2 ABC P 2 EFG J 3 ABD J 3 KFG P 4 ABD P 4 KFG J 5 NKP J 5 KDP P 6 ABC J 6 EFG P Quote Link to comment Share on other sites More sharing options...
odhu_le_macha Posted May 7, 2021 Report Share Posted May 7, 2021 Write Case statement for identifier based upon Id Quote Link to comment Share on other sites More sharing options...
i_sudigadu Posted May 7, 2021 Report Share Posted May 7, 2021 Try function - Row number over partition order by Quote Link to comment Share on other sites More sharing options...
Raazu Posted May 7, 2021 Report Share Posted May 7, 2021 Use decode , hope u can write sql, if not I will get u tomorrow Quote Link to comment Share on other sites More sharing options...
Be_happy Posted May 7, 2021 Report Share Posted May 7, 2021 Both decode and case statements return same result. I prefer Case statement as it has more logical operators than decode.. Quote Link to comment Share on other sites More sharing options...
rajprakashraj Posted May 7, 2021 Author Report Share Posted May 7, 2021 14 hours ago, duryaodhana said: If all the data you have is only the above set of records, then use a CASE statement. no, i have thousands of records of this criteria. Using case and decode will not work as i need to write it for all combinations Quote Link to comment Share on other sites More sharing options...
rajprakashraj Posted May 7, 2021 Author Report Share Posted May 7, 2021 7 hours ago, i_sudigadu said: Try function - Row number over partition order by how row number partition works here? Quote Link to comment Share on other sites More sharing options...
rajprakashraj Posted May 7, 2021 Author Report Share Posted May 7, 2021 7 hours ago, Raazu said: Use decode , hope u can write sql, if not I will get u tomorrow no, i have thousands of records of this criteria. Using case and decode will not work as i need to write it for all combinations. i just provided few samples Quote Link to comment Share on other sites More sharing options...
Raazu Posted May 7, 2021 Report Share Posted May 7, 2021 1 minute ago, rajprakashraj said: no, i have thousands of records of this criteria. Using case and decode will not work as i need to write it for all combinations. i just provided few samples Write a plsql code then! Use nested cursors l Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.