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 Go to next message
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


Combine 2 fields when using Groupby [message #686438 is a reply to message #686437] Tue, 13 September 2022 11:47 Go to previous messageGo to next message
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 #686439 is a reply to message #686438] Wed, 14 September 2022 01:47 Go to previous messageGo to next message
skfaheem024
Messages: 5
Registered: September 2022
Junior Member
Hi,

Can you try this query. Please substitute temp1 with your table name.

SELECT FILEID,LISTAGG(Position||',') WITHIN GROUP (ORDER BY FILEID)
 		 FROM temp1
group by FILEID
Order 	By FILEID;

Re: Combine 2 fields when using Groupby (merged) [message #686442 is a reply to message #686437] Wed, 14 September 2022 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68345
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Combine 2 fields when using Groupby [message #686443 is a reply to message #686439] Wed, 14 September 2022 07:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3200
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 #686444 is a reply to message #686443] Wed, 14 September 2022 07:27 Go to previous messageGo to next message
skfaheem024
Messages: 5
Registered: September 2022
Junior Member
@Solomon, Perfect ! Thank you !
Re: Combine 2 fields when using Groupby [message #686445 is a reply to message #686444] Wed, 14 September 2022 08:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3200
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.
Re: Combine 2 fields when using Groupby [message #686446 is a reply to message #686439] Wed, 14 September 2022 14:17 Go to previous messageGo to next message
halifaxns
Messages: 7
Registered: June 2013
Junior Member
Thank you
Re: Combine 2 fields when using Groupby [message #686447 is a reply to message #686445] Wed, 14 September 2022 14:22 Go to previous message
halifaxns
Messages: 7
Registered: June 2013
Junior Member
Thank you
Previous Topic: Running Balance
Next Topic: XMLTABLE - Multiple children
Goto Forum:
  


Current Time: Fri Feb 03 04:26:03 CST 2023