I have seen the following questions but can’t get them to work for what I’m looking for.
How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
SQL Query to concatenate column values from multiple rows in Oracle
I am using Oracle and would like the solution not to have pl-Sql.
I’m trying to get the query to look as so with a possible new line per each row and delimited with a | or comma :
Data Set:
question_id element_message date -------------------------------------------- 1 hello 10/10/19 2 goodbye 11/11/19
Desired one row query result:
1 | hello | 10/10/19 --new line added in query(if possible) 2 | goodbye| 11/11/19
And if new line in query isn’t possible:
Desired result:
1 | hello | 10/10/19 | 2 | goodbye| 11/11/19 |
*EDIT To to clarify why I am using this. I am using a tool called Geneos with has a row by row limitation in terms of triggering an email. So this is a hack to trigger one email for all the rows per that day.
Advertisement
Answer
Why do you want a one row query if you want each row to be printed on a different line?
If you just wan to run a query that returns your data with no header or page separator use this:
set pagesize 0 select question_id || '|' || element_message || '|' || date from your_table;