Oracle transform table column to two columns

userLuf01 0
Viewed 26

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?

pivot oracle
2 Answers

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

MT00

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

Mahamoutou0
Related