sql - Unpivot a join between two tables with the same column names

Question

I am having trouble with unpivoting the results of a join between two tables. The two tables are defined as:

create table scores_jan (
   id number
 , test_1 number
 , test_2 number
 , test_3 number
 );

create table scores_feb (
   id number
 , test_1 number
 , test_2 number
 , test_3 number
 );

insert into scores_jan values (1, 50, 60, 70);
insert into scores_feb values (1, 55, 65, 75);
commit;

I want to unpivot this to get a row for each ID/test combination, to achieve the result:

ID    TEST_NUMBER    JAN_SCORE    FEB_SCORE
1     test_1         50           55
1     test_2         60           65
1     test_3         70           75

If I write the unpivot without specifying the columns I'm interested in, it looks like this:

select *
  from scores_jan j
  join scores_feb f
    on j.id = f.id
unpivot ( (jan_score, feb_score) for test_name in ( (test_1, test_1) as 'test_1'
                                                  , (test_2, test_2) as 'test_2'
                                                  , (test_3, test_3) as 'test_3'
                                                  )
        )

This generates the error ORA-00918: column ambiguously defined

If I try to write it with specifying the columns to use, it looks like this:

select *
  from scores_jan j
  join scores_feb f
    on j.id = f.id
unpivot ( (jan_score, feb_score) for test_name in ( (j.test_1, f.test_1) as 'test_1'
                                                  , (j.test_2, f.test_2) as 'test_2'
                                                  , (j.test_3, f.test_3) as 'test_3'
                                                  )
        )

This generates the error ORA-01748: only simple column names allowed here

Is there a way to get this unpivot to work? I could put one of the tables into a subquery, but it seems pretty suboptimal to use a subquery just for changing the alias of a column.

Answer 1

P.s.

Could also be solved like this

select      id,test,jan_score,feb_score

from                scores_jan j
            join    (select  id as fid,test_1 as f1,test_2 as f2,test_3 as f3 
                     from    scores_feb
                     ) f
            on      j.id = f.fid
            unpivot ((jan_score,feb_score) for (test) in 
               ((test_1,f1) as 'test_1',(test_2,f2) as 'test_2',(test_3,f3) as 'test_3')) u

order by    test         

Answer 2

select      *

from        (         select 'JAN_SCORE' as mth,id,test_1,test_2,test_3 from scores_jan s
            union all select 'FEB_SCORE'       ,id,test_1,test_2,test_3 from scores_feb s 
            ) unpivot (score for test in (test_1,test_2,test_3))
              pivot (max(score) for mth in ('JAN_SCORE','FEB_SCORE'))

order by    test   

---

+----+--------+-----------+-----------+
| ID | TEST   | JAN_SCORE | FEB_SCORE |
+----+--------+-----------+-----------+
| 1  | TEST_1 | 50        | 55        |
+----+--------+-----------+-----------+
| 1  | TEST_2 | 60        | 65        |
+----+--------+-----------+-----------+
| 1  | TEST_3 | 70        | 75        |
+----+--------+-----------+-----------+

Answer 3

Problem is that the column names are same for the two tables and if you try to use aliases in the UNPIVOT, it'll give error saying - simple names expected.

In Oracle 12c+, use OUTER APPLY:

select j.id, x.*
  from scores_jan j
  join scores_feb f
    on j.id = f.id
outer apply (
    select 'test_1' test_name, j.test_1 jan_score, f.test_1 feb_score from dual union all
    select 'test_2', j.test_2, f.test_2 from dual union all
    select 'test_3', j.test_3, f.test_3 from dual
) x

In order versions, do the join in subquery, do aliasing and then use UNPIVOT:

select * 
from (      
    select 
        j.id,
        j.test_1 j_test_1,
        j.test_2 j_test_2,
        j.test_3 j_test_3,
        f.test_1 f_test_1,
        f.test_2 f_test_2,
        f.test_3 f_test_3
    from scores_jan j
    join scores_feb f
        on j.id = f.id
    ) 
unpivot (
    (jan_score, feb_score) 
    for test_name in (
        (j_test_1, f_test_1) as 'test_1',
        (j_test_2, f_test_2) as 'test_2',
        (j_test_3, f_test_3) as 'test_3'
    )
);

Answer 4

The problem, as you saw, is the ambiguous column names. There are really two problems. One is the id column. You can deal with it by using a different join syntax - instead of on j.id = f.id write using(id). Check the documentation for this join syntax if you are not familiar with it.

However, the ambiguity of the test_1 column name can't be addressed this way, and as you saw, you can't use table qualifiers with unpivot.

The correct solution, in this situation, is to have the entire join as a subquery (not just one of the tables); select from ( join subquery ) unpivot...

Of course, you could change the column names in the tables (to j_test_1, f_test_1 etc.) but that may be too much trouble. If you go that way, the id column name should still be the same in both tables, and you would still need the using(id) syntax.

Answer 5

I don't think "unpivot" is the right approach. If you have only one row per id in each table, then use union all or a similar approach. Here is the idea:

select j.id, j.test_1 as test from scores_jan union all
select j.id, j.test_2 as test from scores_jan union all
select j.id, j.test_3 as test from scores_jan union all
select f.id, f.test_1 as test from scores_feb union all
select f.id, f.test_2 as test from scores_feb union all
select f.id, f.test_3 as test from scores_feb ;

You should also recognize that using separate tables for separate months is a really bad idea. You should be storing all the data in a single table, with a column specifying the month.