Forum Stats

  • 3,735,100 Users
  • 2,247,106 Discussions
  • 7,857,683 Comments

Discussions

Combining multiple rows to get a single one

User_UJ80K
User_UJ80K Member Posts: 1 Green Ribbon
edited Jun 10, 2021 5:27PM in SQL & PL/SQL

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 me with what functions or query should I use to get above mentioned output?

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,531 Red Diamond
    edited Jun 10, 2021 6:27PM

    Hi, @User_UJ80K

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    It looks like you want to pivot.

    Output needed :


    --------------------------------------------------------------

    | Employee Name  | Emp Id | department1 | department2|

    --------------------------------------------------------------

    | Sam       | 101  | Sales    | Finance  |

    --------------------------------------------------------------

    | Dirk      | 102  | marketing  | Research  |

    In the output, do you want one row for each distinct combination of employee_name and emp_id?

    For 'Sam', 101: dos it matter that the departments are in that order, or would you be satisfied with department1='Finance' and department2='Sales'?

    What if there are three (or more) rows in the table with the same employee_name and emp_id?

Sign In or Register to comment.