0

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.

Blawless
  • 1,229
  • 2
  • 16
  • 26
  • Why do you want unrelated data together in a single row? – jarlh May 03 '19 at 09:20
  • We are using a tool called Geneos and it can only render data on a row by row basis. – Blawless May 03 '19 at 09:22
  • 1
    You may modify the `LISTAGG` query in one of the answers in the link you shared and use the delimiter as `chr(10)`( represents a newline) separating each line instead of a comma. But, do note that the overall length after concatenation can't exceed 4000 characters. There are other aggregation techniques discussed [here](https://oracle-base.com/articles/misc/string-aggregation-techniques) to achieve the same which may be useful to you. – Kaushik Nayak May 03 '19 at 15:21

3 Answers3

1

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;
StephaneM
  • 4,779
  • 1
  • 16
  • 33
1

SQL Server solution :You can solve it using self join like below:

select top 1 CONCAT(d1.question_id, ' | ', d1.element_message, ' | ', d1.date, 
'|' d2.question_id, ' | ', d2.element_message, ' | ', d2.date) 
from data d1 join data d2 on (d1.question_id < d2.question_id and)
Jeet Kumar
  • 555
  • 3
  • 12
0

Is this what you're looking for?

SELECT CONCAT(question_id, ' | ', element_message, ' | ', date) FROM Data
codepenguin1
  • 1
  • 1