Forum Stats

  • 3,839,402 Users
  • 2,262,488 Discussions
  • 7,900,958 Comments

Discussions

Categories

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?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,250 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?

    · Share on TwitterShare on Facebook
  • Kalaiarasu.Pandian
    Kalaiarasu.Pandian Member Posts: 3 Green Ribbon


    create table emp1 (employeename varchar2(4), department varchar2(9), empid number);


    insert into emp1 values ('Sam', 'Sales', 101);

    insert into emp1 values ('Sam', 'Finance', 101);

    insert into emp1 values ('Dirk', 'Research', 102);

    insert into emp1 values ('Dirk', 'Marketing', 102);


    select employeename, empid, substr ( a , 1, (instr(a, ',', 1))-1) department1, 

    substr ( b , 1, (instr(b, ',', 1))-1) department2 from

    (select employeename, empid, listagg(department, ',') within group (order by department desc) 

    over (partition by empid) a, listagg(department, ',') within group (order by department asc) 

    over (partition by empid) b from emp1) group by employeename, empid, a, b;


    EMPL    EMPID DEPARTMENT1 DEPARTMENT2

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

    Sam      101 Sales Finance

    Dirk     102 Research Marketing

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,250 Red Diamond

    Hi, @User_NFBSP

    Thanks for posting the sample data. Don't forget to answer the questions in the first reply, and to post your requirements )that is, why you want those results given that data) and your full Oracle version number.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,250 Red Diamond

    Hi, @User_NFBSP

    It looks like the query you posted produces one row of output for each distinct combination of employeename and empid, including the last department (in alphabetic order) related to them in the department1 column and the first department (in alphabetic order) in the departmebt2 column. (If there is only one distinct department for an employeename/empid combinatio, it will appear in both the depratmet and department2 columns,) If that's what you want to do, here's a simpler, more efficient way to do it:

    SELECT   employeename, empid
,	 MAX (department)	AS department1
,	 MIN (department)	AS department2
FROM	 emp1
GROUP BY employeename, empid
ORDER BY employeename, empid	-- or whatever you want
;


    · Share on TwitterShare on Facebook