How to Concatenate Text from Multiple Rows into a Single Text String in Oracle Server

How to concatenate text from multiple rows into a single text string in Oracle server?

Have you tried this?

select end_date,
       listagg(CLOSE_DATE, ',') within group (order by CLOSE_DATE DESC) as close_dates,
       listagg(id, ',') within group (order by id) as ids
from TBL_S_PLCLOSEDATE
where D_END = date '2018-05-18'
group by end_date;

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

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;

SQL Query to concatenate column values from multiple rows in Oracle

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Then join to A to pick out the pids you want.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.

Oracle - SQL to concatenate multiple rows

If you have Oracle 11g you could use the LISTAGG() function for this:

SELECT
      id
    , listagg(str) WITHIN GROUP (ORDER BY OFFSET) AS str_of_str
FROM yourtable
GROUP BY id

see: http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm
and this sqlfiddle

  | ID |               STR_OF_STR |
    |----|--------------------------|
    |  1 | TestSTR1TestSTR3TestSTR5 |
    |  2 |         TestSTR4TestSTR2 |
    |  3 |                 TestSTR6 |

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.

Concatenate across columns and rows using group and listagg in Oracle SQL

Use the following query where you will directly get concatenated column values:

SELECT
    "GROUP",
    LISTAGG(VAL_1 || VAL_2 || VAL_3)  
        WITHIN GROUP(ORDER BY VAL_1) AS "TEXT"
FROM DATA
GROUP BY "GROUP";

Note: Do not use oracle reserved keywords as the column names. Here GROUP is the oracle reserved keyword.

Cheers!!

Concatenate and group multiple rows in Oracle

Consider using LISTAGG function in case you're on 11g:

select grp, listagg(name,',') within group( order by name ) 
  from name_table group by grp

sqlFiddle

upd: In case you're not, consider using analytics:

select grp,
    ltrim(max(sys_connect_by_path
       (name, ',' )), ',')
        scbp
  from (select name, grp,
            row_number() over
           (partition by grp
            order by name) rn
         from tab
          )
start with rn = 1
connect by prior rn = rn-1
and prior grp = grp
  group by grp
  order by grp

sqlFiddle

How to combine all records into one row | ORACLE SQL |

Here's one way, using LISTAGG to illustrate the method. If you have too many tables, you will need a different way of aggregating (such as XMLAGG), but that's a different issue - you can find hundreds of questions about that here on SO.

Best to use UNION ALL rather than UNION, but if you insist on just UNION, you can modify the solution yourself. Use LISTAGG, but don't include UNION [ALL] in the tokens to be aggregated; rather, use it as the delimiter!

For easier reading of the output, I included a newline in the delimiter as well; that's not needed except for debugging. You may choose to remove it after you are satisfied that the query works as expected.

Also, I didn't bother to concatenate a semicolon at the end; you can add that yourself.

NOTE - the output is a single row, meaning a single string that contains newline characters (and therefore appears as multiple lines of text). It's NOT spread over multiple rows, it's just several lines in a single string in a single row.

EDIT Looking at it again, I see that your individual SELECT statements are incorrect (they show the keyword "from" twice, and all SELECT statements are counting the rows in the get_tables table instead of in each respective table, and perhaps other mistakes as well). I will let you fix all those mistakes; they are unrelated to the main topic of your question, which was about aggregating the statements - whether correct or otherwise - in the desired way.

END EDIT (FURTHER EDIT AT THE BOTTOM OF THE ANSWER)

with get_tables (tbl_names) as (
  select 'EMP_1'           from dual union all
  select 'EMP_2'           from dual union all
  select 'STUD_1'          from dual union all
  select 'STUD_2'          from dual union all
  select 'STUD_3'          from dual union all
  select 'STUDENT_DETAILS' from dual
)
-- end of sample data, for testing only; remove WITH clause
-- and use your actual table and column names.
select listagg('SELECT ' ||  '''' || tbl_names || '''' ||
         ' AS TBL , COUNT(*) as CNT FROM  from  tbl_names',
               ' union all' || chr(10))
         within group (order by tbl_names) as sql_str
from get_tables;

SQL_STR                                                                           
----------------------------------------------------------------------------------
SELECT 'EMP_1' AS TBL , COUNT(*) as CNT FROM  from  tbl_names union all
SELECT 'EMP_2' AS TBL , COUNT(*) as CNT FROM  from  tbl_names union all
SELECT 'STUDENT_DETAILS' AS TBL , COUNT(*) as CNT FROM  from  tbl_names union all
SELECT 'STUD_1' AS TBL , COUNT(*) as CNT FROM  from  tbl_names union all
SELECT 'STUD_2' AS TBL , COUNT(*) as CNT FROM  from  tbl_names union all
SELECT 'STUD_3' AS TBL , COUNT(*) as CNT FROM  from  tbl_names

SECOND EDIT

The OP's use case requires the generation of a CLOB, so LISTAGG won't work. Here's how the query can be modified to work for CLOB output. I corrected the statement at the same time - see my first EDIT, before the first code block.

To make sure this works correctly, I wrote it for the ALL_TABLES view in my schema; the table names are in a column called TABLE_NAME. The OP should change these names for his use case.

select regexp_replace(
         xmlcast(xmlagg(xmlelement(e, 
           'select ''' || table_name || ''' as tbl, count(*) as cnt from '
           || table_name, ' union all ' || chr(10))) as clob),
       ' union all ' || chr(10) ||'$', ';')
       as sql_string
from   all_tables;

END SECOND EDIT

---

Related Topics