Home » SQL & PL/SQL » SQL & PL/SQL » Combine 2 fields when using Groupby (merged) (Oracle DB 18c)
Combine 2 fields when using Groupby (merged) [message #686437] |
Tue, 13 September 2022 11:46 |
|
halifaxns
Messages: 7 Registered: June 2013
|
Junior Member |
|
|
Hi there
Is there a way combine 2 fields(from different rows) when using GroupBY
For instance, the result below comes from a query that uses Groupby,
FileID Position
218625 Staff Accountant
218625 Mechanical Designer
The client would like to see this in one row, like below :
FileID Position
218625 Staff Accountant, Mechanical Designer
Many thanks in Advance,
Cheers,
E
|
|
|
|
|
|
Re: Combine 2 fields when using Groupby [message #686443 is a reply to message #686439] |
Wed, 14 September 2022 07:15 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
skfaheem024 wrote on Wed, 14 September 2022 02:47
SELECT FILEID,LISTAGG(Position||',') WITHIN GROUP (ORDER BY FILEID)
FROM temp1
group by FILEID
Order By FILEID;
Not LISTAGG(Position||',') but rather LISTAGG(Position,','). See the difference:
select listagg(dname || ',') within group(order by dname) skfaheem024,
listagg(dname,',') within group(order by dname) sy
from dept
/
SKFAHEEM024 SY
------------------------------------- ------------------------------------
ACCOUNTING,OPERATIONS,RESEARCH,SALES, ACCOUNTING,OPERATIONS,RESEARCH,SALES
SQL>
SY.
|
|
|
|
Re: Combine 2 fields when using Groupby [message #686445 is a reply to message #686444] |
Wed, 14 September 2022 08:41 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
In addition OP might need DISTINCT:
select deptno,
listagg(job,',') within group(order by job) job_list,
listagg(distinct job,',') within group(order by job) distinct_job_list,
sum(sal) dept_salary
from emp
group by deptno
/
DEPTNO JOB_LIST DISTINCT_JOB_LIST DEPT_SALARY
---------- -------------------------------------------------- ----------------------- -----------
10 CLERK,MANAGER,PRESIDENT CLERK,MANAGER,PRESIDENT 8750
20 ANALYST,ANALYST,CLERK,CLERK,MANAGER ANALYST,CLERK,MANAGER 10875
30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN CLERK,MANAGER,SALESMAN 9400
SQL>
However DISTINCT isn't available in older versions. For example, on 12C:
SQL> select deptno,
2 listagg(job,',') within group(order by job) job_list,
3 listagg(distinct job,',') within group(order by job) distinct_job_list,
4 sum(sal) dept_salary
5 from emp
6 group by deptno
7 /
listagg(distinct job,',') within group(order by job) distinct_job_list,
*
ERROR at line 3:
ORA-30482: DISTINCT option not allowed for this function
SQL>
So we would need to use something like:
with t as (
select deptno,
case row_number() over(partition by deptno,job order by 1)
when 1 then job
end job,
sal
from emp
)
select deptno,
listagg(job,',') within group(order by job) distinct_job_list,
sum(sal) dept_salary
from t
group by deptno
/
DEPTNO DISTINCT_JOB_LIST DEPT_SALARY
---------- ------------------------------ -----------
10 CLERK,MANAGER,PRESIDENT 8750
20 ANALYST,CLERK,MANAGER 10875
30 CLERK,MANAGER,SALESMAN 9400
SQL>
SY.
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 29 03:59:32 CDT 2024
|