Oracle concatenate in one column, display in multiple line-CodePudding

I need to concatenate a few column into one and I'm using || to do that. The result successfully display all the data in one column. However, I need it to display in multiple line in that one column. Is that possible?

Here is my current code:

SELECT addr1 || CASE WHEN addr2 IS NOT NULL THEN ', '|| addr2 END               
             || CASE WHEN addr3 IS NOT NULL THEN ', '|| addr3 END               
             || CASE WHEN addr4 IS NOT NULL THEN ', '|| addr4 END               
             || CASE WHEN addr5 IS NOT NULL THEN ', '|| addr5 END AS Address
  FROM addressdata

And this is what I want it to display as:

I'm still new to this Oracle and database thing. Really appreciate your help.

Thank you in advance. :D

CodePudding user response：

Instead of a comma (as a separator), use newline (chr(10)) or - maybe even better - CRLF (carriage return line feed) (chr(13) || chr(10)) character:

SELECT addr1 || CASE WHEN addr2 IS NOT NULL THEN CHR(13)||CHR(10) || addr2 END               
             || CASE WHEN addr3 IS NOT NULL THEN CHR(13)||CHR(10) || addr3 END               
             || CASE WHEN addr4 IS NOT NULL THEN CHR(13)||CHR(10) || addr4 END               
             || CASE WHEN addr5 IS NOT NULL THEN CHR(13)||CHR(10) || addr5 END AS Address
FROM addressdata

CodePudding user response：

An option would be using a dynamic SQL Select statement in order to profit LISTAGG() function containing Carriage return character along with the columns of the similar names(all those starts with addr) such as

SET SERVEROUTPUT ON
DECLARE
  v_str    VARCHAR2(1500);
  v_table  VARCHAR2(100):='ADDRESSDATA';
BEGIN
  SELECT LISTAGG(c.column_name,'||'',''||CHR(13)||') WITHIN GROUP (ORDER BY c.column_id)
    INTO v_str
    FROM user_tab_cols c
   WHERE c.table_name = v_table   
     AND c.column_name LIKE 'ADDR%';

  EXECUTE IMMEDIATE 'SELECT '||v_str||
                     ' FROM '||v_table INTO v_str; 
                      
  DBMS_OUTPUT.PUT_LINE(v_str);     
END;
/

Assuming your columns are sorted within the table as presented like addr1, addr2, addr3 ..etc(to consider the current ORDER BY clause)

Demo