Skip to Main Content
  • Questions

  • Data representation in non-vertical mode throwing "ORA-00918: column ambiguously defined"

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sagar.

Asked: May 13, 2019 - 9:59 am UTC

Last updated: May 14, 2019 - 1:24 pm UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

Hi Team

Please refer to Live SQL link for re-producible data.

Fetch info about departments assigned to users:
SELECT user_login, dept_id, dept_name
FROM users, dept
WHERE users.user_key=dept.user_key;

USER_LOGIN       DEPT_ID DEPT_NAME     
------------- ---------- --------------
User1                  1 Physics       
User1                  2 Maths         
User2                  3 Physics       
User2                  4 Maths


I would like to present output in below format. 
USER_LOGIN       DEPT_ID_1 DEPT_NAME_1 DEPT_ID_2     DEPT_NAME_2
-------------    ------------   ------------ -----------   -------------    
User1            1  Physics  2       Maths 
User2            3  Physics  4       Maths


This is throwing me "ORA-00918: column ambiguously defined" exception.
select user_login,
 max( decode(rn,1,dept_id) ) c1_1,
 max( decode(rn,1,dept_name) ) c2_1,
 max( decode(rn,2,dept_id) ) c1_2,
 max( decode(rn,2,dept_name) ) c2_2
from (
        select  users.user_login, 
                dept.dept_id, 
                dept.dept_name, 
                row_number() over (partition by user_login order by rowid) rn
        from users 
            JOIN dept ON users.user_key=dept.user_key
     )
group by user_login;


Thanks!

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/idq9e3t0zp7vybgl0vmhsrrj7

and Chris said...

The problem is here:

row_number() over (
  partition by user_login 
  order by rowid
)


You've accessed two tables. The database doesn't know which one to get the rowid from!

You can fix this by prefixing rowid with the name/alias for one of the tables.

But ordering by rowid is a bad idea. It's better to pick a real column in the table. And you can use the pivot clause, saving you from typing all those max expressions:

with rws as (
  select users.user_login, 
         dept.dept_id, 
         dept.dept_name, 
         row_number() over (
           partition by user_login 
           order by dept_id
         ) rn
  from users 
  JOIN dept 
  ON users.user_key=dept.user_key
)
  select * from rws
  pivot (
    max(dept_id) id, max (dept_name) name 
    for rn in ( 1, 2 )
  );

USER_LOGIN   1_ID   1_NAME    2_ID   2_NAME   
User1           1   Physics      2   Maths     
User2           3   Physics      4   Maths


If you want to know more about this, see https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Fetch all matching data

Sagar, May 14, 2019 - 9:03 am UTC

Thanks Chris.

Instead of manually doing it for few users (IN clause for 1,2), I want this SQL to print data for all users in USERS table.

For this I tried two variations, but both will generate XML as PIVOT XML is being used. As this is the only supported option of doing sub-query inside IN clause of PIVOT - am I right?

How can I achieve this without printing out XML data?

Thanks!

with rws as (
  select users.user_login, 
         dept.dept_id, 
         dept.dept_name, 
         row_number() over (
           partition by user_login 
           order by dept_id
         ) rn
  from users 
  JOIN dept 
  ON users.user_key=dept.user_key
)
  select * from rws
  pivot xml(
    max(dept_id) id, max (dept_name) name 
    for rn in ( select rn from rws )
  );

OR

with rws as (
  select users.user_login, 
         dept.dept_id, 
         dept.dept_name, 
         row_number() over (
           partition by user_login 
           order by dept_id
         ) rn
  from users 
  JOIN dept 
  ON users.user_key=dept.user_key
)
  select * from rws
  pivot xml(
    max(dept_id) id, max (dept_name) name 
    for rn in ( any )
  );

USER_LOGIN    RN_XML                                                                          
------------- --------------------------------------------------------------------------------
User1         <PivotSet><item><column name = "RN">1</column><column name = "ID">1</column><col
User2         <PivotSet><item><column name = "RN">1</column><column name = "ID">3</column><col

User1:
<?xml version="1.0" encoding="UTF-8"?>
<PivotSet>
   <item>
      <column name="RN">1</column>
      <column name="ID">1</column>
      <column name="NAME">Physics</column>
   </item>
   <item>
      <column name="RN">2</column>
      <column name="ID">2</column>
      <column name="NAME">Maths</column>
   </item>
</PivotSet>

User2:
<?xml version="1.0" encoding="UTF-8"?>
<PivotSet>
   <item>
      <column name="RN">1</column>
      <column name="ID">3</column>
      <column name="NAME">Physics</column>
   </item>
   <item>
      <column name="RN">2</column>
      <column name="ID">4</column>
      <column name="NAME">Maths</column>
   </item>
</PivotSet>

Chris Saxon
May 14, 2019 - 10:15 am UTC

This will work for all users? You'll only have an issue if users have more than 2 departments.

So provided you can define a "reasonable" upper limit for how many departments a user's associated with, you can list that many values in the FOR RN IN clause.

If you can't, there's no in-built way to support dynamic pivot. Other than XML. You can convert XML to relational rows-and-columns with XMLTable. But you need to know the upper limit for departments/user to do that...

For other dynamic pivoting options, see:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:5312784100346298561

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#pivot

PS - I feel like the user-dept relationship is the wrong way around. Surely dept_id is a property of users? Or are these the people who own/manage the departments?

Thanks

Sagar, May 14, 2019 - 12:18 pm UTC

Thanks Chris. Got your point.
It won't span many departments, so this approach will be fine for this use case.
But if dynamic requirement is there, other options need to be looked into.
Chris Saxon
May 14, 2019 - 1:24 pm UTC

Happy to help.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.