Is There an Oracle SQL Query That Aggregates Multiple Rows into One Row

How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.

This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )

SELECT questionid,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM   (SELECT questionid,
               elementid,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
        FROM   emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;

Oracle SQL - Multiple rows into one field

Use listagg after getting the distinct delivery types per product id. (Note that there is a 4000 character limit for the aggregated string.)

select product_id,listagg(delivery_type,'/') within group (order by delivery_type)
from (select distinct product_id,delivery_type from tbl) t
group by product_id

combine multiple rows into a single row in Oracle?

The function that you need is listagg() with does string concatenation when aggregating. You also need to first concatenate the ids together:

select id,
       listagg(m_id || ',' || s_m_id, ';') within group (order by m_id) as merge_ids
from test t
group by id;

By the way, the result data is incorrect (because the id is the same on all three rows). This probably accounts for the downvote.

EDIT (in response to comment):

You have two separators in the original example, one is comma (between ids) and one is a semicolon (between rows). You can replace either with '|' to get a pipe separator.

Convert multiple rows in to single row in SQL using Case-When

First, you should learn to use proper explicit JOIN syntax. Even Oracle now recommends its use.

Second, your SELECT needs aggregation functions. I would recommend something like this:

SELECT  P.APPDISPLAYNAME AS PLAYERNAME ,
        MAX(CASE WHEN GS.SETNUMBER = 1 THEN SS.SETSCORE END) AS set1,
        MAX(CASE WHEN GS.SETNUMBER = 2 THEN SS.SETSCORE END) AS set2,
        MAX(CASE WHEN GS.SETNUMBER = 3 THEN SS.SETSCORE END) AS set3,
        MAX(CASE WHEN GS.SETNUMBER = 4 THEN SS.SETSCORE END) AS set4
. . .

Arithmetic combination of multiple rows into one row - Oracle SQL

I am assuming that what you have shown is your question is table and what you want is some of the constants and aggregation of values.

Use group by with aggregate function as following:

Select date, building, 'W' as metric,
       Sum(case when metric in ('Y', 'Z') then performance end) 
      / sum(case when metric ='X' then performance end)
From your_table
Where metric in ('X','Y','Z')
Group by date, building
Having count(distinct metric) = 3

Also, handle the situation where performance against X is not 0 by adding condition on sum of X.

Cheers!!

Aggregate function to sum values from multiple rows into one result row

This is not a "pivot" problem. You do not want separate columns for each value. You just want to concatenate them. Use listagg():

select class, listagg(name, ',') within group (order by weight) as names, sum(weight)
from t
group by class;

---

Related Topics