Hi

I have a requirement to write a select that will produce multiple rows based on a single row.

For eg:

my columns and data in a table are as follows:

code category1 category2 category3
AB B P O
BC B B P
CD B NONE NONE

so my select statement should produce something like this
code category
AB B
AB P
AB O
BC B
BC P
CD D


IF The values in category1,category2,category3 are different then it should produce three rows, if they have two distinct values it should produce two rows, if a category is none, then it should consider it as null .

I know we can use unpivot to split the columns into rows but i don't know how to use conditions in unpivot.

Can someone please help me in writing the query.

Cheers
Sirisha

with rws as (
  select 1 x, 'ABC' c1, 'DEF' c2, 'GHI' c3 from dual union all
  select 2 x, 'ABC' c1, 'DEF' c2, 'DEF' c3 from dual union all
  select 3 x, 'ABC' c1, null c2, null c3 from dual 
)
  select distinct x, val from rws
  unpivot (
    val for col in (c1, c2, c3)
  )
  order  by 1, 2;

X  VAL  
1  ABC  
1  DEF  
1  GHI  
2  ABC  
2  DEF  
3  ABC