sql experts

[rajprakashraj]

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

[teluguking]

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

[duryaodhana]

If all the data you have is only the above set of records, then use a CASE statement. 

[zarathustra]

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

[odhu_le_macha]

Write Case statement for identifier based upon  Id

[i_sudigadu]

Try function - Row number over partition order by 

[Raazu]

Use decode , hope u can write sql, if not I will get u tomorrow 

[Be_happy]

Both decode  and case statements return same result.

I prefer Case statement as it has more logical operators than decode..

[rajprakashraj]

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

[rajprakashraj]

7 hours ago, i_sudigadu said:

Try function - Row number over partition order by 

how row number partition works here?

[rajprakashraj]

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

[Raazu]

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