Oracle query to migrate some columns into one

Question

I have a table, which looks like this:

• There's a primary key (PK). I need it (obviously).
• AA, BB and CC has 6 versions (up to AA5, BB5, CC5). All of them strings.
  • AA, BB and CC are bound to each other: if there's AA, there should be BB and CC.
  • They have to be filled continuously: there is no such situation where AA, BB, CC are filled, AA2, BB2, CC2 not filled, and AA3, BB3, CC3 are filled!
• There are other columns, but I don't need them.

PK AA BB CC AA2 BB2 CC2 AA3 BB3 CC3 1 X Y Z D F G 2 Q W E 3 U I O P H K L R M

What I want to do with the query:

• I need PK.
• All AA should be placed into 1 column called AAA.
• All BB should be placed into 1 column called BBB.
• All CC should be placed into 1 column called CCC.

PK AAA BBB CCC 1 X Y Z 1 D F G 2 Q W E 3 U I O 3 P H K 3 L R M

Is this possible?

Answer 1

You can use UNPIVOT

EDIT: There was no need of JOIN as in my original answer, thanks to @mathguy, I was able to correct it.

select pk,AAA,BBB,CCC FROM YOURTABLE 
UNPIVOT 
  ( (AAA,BBB,CCC) 
      FOR col in ( (aa, bb, cc), (aa2, bb2, cc2), (aa3, bb3, cc3) )
   ) 

DEMO

Answer 2

Before the unpivot operator, unpivoting was done with a cross join to a small table (single column, as many rows as columns or groups of columns need to be unpivoted: in your case, six - or in the simplified example, three).

The reason for this approach - vs. the simple but inefficient union all approach - is that you only need to read the base table once.

Like this: ("table1" is the name of your base table)

select t.pk,
       case h.lvl  when 1 then aa
                   when 2 then aa2
                   when 3 then aa3 
            end as aa,
       case h.lvl  when 1 then bb
                   when 2 then bb2
                   when 3 then bb3
            end as bb,
       case h.lvl  when 1 then cc
                   when 2 then cc2
                   when 3 then cc3
            end as cc
from   table1 t
       cross join
       ( select level as lvl from dual connect by level <= 3 ) h
;

NOTE: This will also produce rows where aa, bb and cc are NULL (when, for example, aa3, bb3 and cc3 were null in the base table). These can be removed, if needed, by wrapping the cross join within an outer query and adding a where clause, for example: where aa is not null.

UNPIVOT

Here is how this can be done in Oracle 11.1 and higher. Groups of columns can be unpivoted in a single unpivot operation, like so:

select  pk, aa, bb, cc
from    table1
unpivot ( (aa, bb, cc) for rn in ( (aa, bb, cc), (aa2, bb2, cc2), (aa3, bb3, cc3) ) );

Answer 3

Use UNION ALL operator:

SELECT pk, aa As aaa,bb as bbb,cc as ccc 
FROM table1

UNION ALL

SELECT pk, aa2,bb2,cc2
FROM table1
WHERE aa2 IS NOT NULL

UNION ALL

SELECT pk, aa3,bb3,cc3
FROM table1
WHERE aa3 IS NOT NULL

Demo: http://sqlfiddle.com/#!4/6a7487/4

| PK | AAA | BBB | CCC |
|----|-----|-----|-----|
|  1 |   D |   F |   G |
|  1 |   X |   Y |   Z |
|  2 |   Q |   W |   E |
|  3 |   P |   H |   K |
|  3 |   L |   R |   M |
|  3 |   U |   I |   O |