Discussions
Categories
- 197.2K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Combining multiple rows to get a single one

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
-
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?
-
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
-
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.
-
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 ;