How can i add a tabulator character to my query

Question

I wrote 3 queries:

   select column1 from table_name1 where -- conditions) 
   ||' TEXT2: '|| (select column2 from table_name2 where  -- conditions) 
   ||' TEXT3: '|| (select column3 from table_name3 where  -- conditions)

This seems to work. Here is the output:

column1 'TEXT2:' column2 'TEXT3:' column3

I tried to use CHR(09), but I think I can't use it properly or it's not working with our system - the 3 select statements are part of an internal query in our system in EAS (-enterprise application software) - I tried it with this format query1||CHR(09)||query2 but it doesn't put the tab into the output.

Expected output in rows:

'TEXT1:' column1||tab character||'TEXT2:' column2||tab character||'TEXT3:' column3

Maybe i should try to use lpad(' ',8) to put whitespace into it ?

How can i achieve the expected output ?

Answer 1

Assuming that the type of all 3 columns is text(VARCHAR2/CHAR) , you may first do a UNION ALL and then apply LISTAGG with CHR(9) as delimiter.

SELECT LISTAGG(text 
               || ': ' 
               || col, CHR(9)) 
         within GROUP( ORDER BY text ) 
FROM   (SELECT column1 AS col, 
               'TEXT1' AS text 
        FROM   table_name1 --where conditions 
        UNION ALL 
        SELECT column2 AS col, 
               'TEXT2' AS text 
        FROM   table_name2 --where conditions 
        UNION ALL 
        SELECT column3 AS col, 
               'TEXT3' AS text 
        FROM   table_name3 --where conditions    
       ); 

Answer 2

Please Try this:

((select ' TEXT1: '||column1 from table_name1 where conditions) ||CHR(09)||' TEXT2: '|| (select column2 from table_name2 where conditions) ||'CHR(09)||'TEXT3: '|| (select column3 from table_name3 where conditions))