Skip to Main Content
  • Questions
  • Combining multiple rows to get a single one

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Isan.

Asked: June 10, 2021 - 4:04 pm UTC

Last updated: June 14, 2021 - 12:05 pm UTC

Version: Oracle 11g

Viewed 100+ times

You Asked

Source table :

----------------------------------------
| Employee Name   | department | Emp Id |
----------------------------------------
| Sam             | Sales      | 101    |
----------------------------------------
| Sam             | Finance    | 101    |
----------------------------------------
| Dirk            | marketing  | 102    |
----------------------------------------
| Dirk            | Research   | 102    |
----------------------------------------


Output needed :

------------------------------------------------------
| Employee Name   | Emp Id | department1 | department2|
------------------------------------------------------
| Sam             | 101    | Sales       | Finance    |
------------------------------------------------------
| Dirk            | 102    | marketing   | Research   |
------------------------------------------------------


Can you kindly help with what functions or query should i use to get above mentioned output?

and we said...

It sounds like you want to pivot.

If you want a column for each of the N departments an employee has, then:

- In a subquery assign row numbers for each department/employee, starting at one for each employee
- Take the max department name for each row number in the pivot clause

Which looks something like:

with rws as (
  select t.*,
         row_number() over (
           partition by emp_id
           order by department
         ) rn
  from   ... t
)
  select * from rws
  pivot (
    max ( department ) dept 
    for rn in ( 1, 2 )
  )


For full details on how to use pivot, see https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

More to Explore

Analytics

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