0

I'm very new to Oracle. I'd like generate another table from the original one. Here is my original table.

Function | Machine | Value
============================
    A    |   M1    | VALID
    A    |   M2    | INVALID
    B    |   M1    | VALID
    B    |   M2    | INVALID
    C    |   M1    | INVALID
    C    |   M2    | VALID

Here is the result table I want to generate.

Function |   M1    |   M2
============================
    A    |  VALID  | INVALID
    B    |  VALID  | INVALID
    C    | INVALID | VALID

Is this possible? I appreciate any suggestion.

user272735
  • 10,473
  • 9
  • 65
  • 96
user3639258
  • 13
  • 1

3 Answers3

1

Use pivot

select * from 
table1
pivot
(
   max("Value")
  for "Machine" in ('M1', 'M2')
      )

fiddle

G one
  • 2,679
  • 2
  • 14
  • 18
0

In case you are using a lower version of oracle say 10, where pivot function is not supported, the following query would be of help to you:

with tab as
(
  SELECT 'A' FUNCTION, 'M1' MACHINE, 'VALID' VALUE FROM DUAL
  UNION 
  SELECT 'A' FUNCTION, 'M2' MACHINE, 'INVALID' VALUE FROM DUAL
  UNION
  SELECT 'B' FUNCTION, 'M1' MACHINE, 'VALID' VALUE FROM DUAL
  UNION 
  SELECT 'B' FUNCTION, 'M2' MACHINE, 'INVALID' VALUE FROM DUAL
  UNION
  SELECT 'C' FUNCTION, 'M1' MACHINE, 'INVALID' VALUE FROM DUAL
  UNION 
  SELECT 'C' FUNCTION, 'M2' MACHINE, 'VALID' VALUE FROM DUAL
)
SELECT FUNCTION, 
  max(case when MACHINE = 'M1' THEN VALUE ELSE ' ' END) M1,
  max(case when MACHINE = 'M2' THEN VALUE ELSE ' ' END) M2
FROM tab
group by FUNCTION
order by FUNCTION;
ngrashia
  • 9,869
  • 5
  • 43
  • 58
-1

Basically you need to pivot the data, if you are using Oracle 11g, you can use the pivot function otherwise you can use the Case or Decode functions as given in this article Oracle Pivot

Example:

pivot 
(
   count(field_name)
   for field_name in ('value1','value2','value3'...)
)

Hope it Helps

MarmiK
  • 5,639
  • 6
  • 40
  • 49
vishad
  • 1,134
  • 7
  • 11