0

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?

Asaf
  • 37
  • 4
  • Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – astentx Sep 13 '22 at 08:12

2 Answers2

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
0

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

Mahamoutou
  • 1,555
  • 1
  • 5
  • 11