Skip to Main Content
  • Questions

  • Combining multiple rows to get a single one

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 1000+ 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 Chris 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

We're not taking comments currently, so please try again later if you want to add a comment.
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.