Oracle transform table column to two columns-CodePudding

Let say I have the next table:

ID_1	ID_2	Value
1	11	A
2	12	A
2	13	A
2	13	B
3	12	A
3	13	B

I want to transform it to:

ID_1	ID_2	Value_A	Value_B
1	11	1	0
2	12	1	0
2	13	1	1
3	12	1	0
3	13	0	1

When there is a value A but no Value B, so put 0 in Value B
When there is a value B but no Value A, so put 0 in Value A

How can I do it in oracle?

CodePudding user response：

Use a PIVOT:

SELECT *
FROM   table_name
PIVOT ( COUNT(*) FOR value IN ('A' AS Value_A, 'B' AS Value_B) )

Which, for the sample data:

CREATE TABLE table_name (ID_1, ID_2, Value) AS
SELECT 1, 11, 'A' FROM DUAL UNION ALL
SELECT 2, 12, 'A' FROM DUAL UNION ALL
SELECT 2, 13, 'A' FROM DUAL UNION ALL
SELECT 2, 13, 'B' FROM DUAL UNION ALL
SELECT 3, 12, 'A' FROM DUAL UNION ALL
SELECT 3, 13, 'B' FROM DUAL;

Outputs:

ID_1	ID_2	VALUE_A	VALUE_B
1	11	1	0
3	12	1	0
2	12	1	0
3	13	0	1
2	13	1	1

fiddle

CodePudding user response：

You could use below solution to get the job done

select ID_1, ID_2
  , decode(Value_A, null, 0, 1) Value_A
  , decode(Value_B, null, 0, 1) Value_B
from your_Table t
pivot (
max(Value) for value in (
  'A' as Value_A
, 'B' as Value_B  
)
)
order by ID_1, ID_2
;

demo