Oracle SQL: force carriage return / new line in output text file

Question

I am new to Oracle SQL and hope someone here can help me with this.

I have a Select statement that is using Listagg to combine multiple columns / values. The output of the SQL job is a text file which then gets manually imported into Excel.

My query itself works but I would like to have some carriage returns / new lines forced in the resulting text file so that not all data from my Listagg appears in one line.

I tried || ' chr(10) column name: ' || and also || ' CHAR(10) column name: ' || between the columns I am aggregating but when importing the text file Excel does not recognise this as a new line (within the same cell) and just shows it as text and all in the same line.

Can someone tell me how I can achieve this ?

Update

My Query (first part):

SELECT
    a.SHIPPER_ID
    , LISTAGG
    (
        'Item No.: ' || ducsi.ITEM_NUMBER || 
        ' chr(10)Article: ' || c.ARTICLE || 
        ' chr(10)Quantity: ' || b.QUANTITY || 
        ' chr(10)chr(10)'
    ) WITHIN GROUP (ORDER BY b.SHIPMENT_ID)
/* ... */

Current Output:

Item No.: 123 chr(10)Article: Item A chr(10)Quantity: 10 ...

Expected Output:

Item No.: 123  
Article: Item A  
Quantity: 10  
...

Many thanks in advance, Mike

Answer 1

The reason why you're getting chr(10) as text is because, well, you're including it in the text string. You have to concatenate it into the text string instead, e.g. 'some text'||chr(10)||'some more text'

However, I think listagg is not really what you should be using here, especially since there's a limit on how much you can output in the result string.

Instead, I think you're after an UNPIVOT, something like:

WITH sample_data AS (SELECT 1 shipper_id, 10 item_number, 'abc' article, 100 quantity FROM dual UNION ALL
                     SELECT 2 shipper_id, 20 item_number, 'efg' article, 200 quantity FROM dual UNION ALL
                     SELECT 3 shipper_id, 30 item_number, 'hij' article, 300 quantity FROM dual)
-- end of mimicking a table called sample_data with data in it
-- see SQL below:
SELECT CASE WHEN row_number() OVER (PARTITION BY shid
                                    ORDER BY CASE WHEN column_name = 'Item No.:' THEN 1
                                                  WHEN column_name = 'Article:' THEN 2
                                                  WHEN column_name = 'Quantity:' THEN 3
                                                  ELSE 4
                                             END) = 1
                 THEN shid
       END shipper_id,
       column_name||' '||vals results
FROM   (SELECT shipper_id shid,
               to_char(item_number) "Item No.:",
               article "Article:",
               to_char(quantity) "Quantity:"
        FROM   sample_data)
UNPIVOT (vals FOR column_name IN ("Item No.:", "Article:", "Quantity:"))
ORDER BY shid,
         CASE WHEN column_name = 'Item No.:' THEN 1
              WHEN column_name = 'Article:' THEN 2
              WHEN column_name = 'Quantity:' THEN 3
              ELSE 4
         END;

SHIPPER_ID RESULTS
---------- --------------------------------------------------
         1 Item No.: 10
           Article: abc
           Quantity: 100
         2 Item No.: 20
           Article: efg
           Quantity: 200
         3 Item No.: 30
           Article: hij
           Quantity: 300

The row_number() analytic function is being used here to ensure the shipper_id is displayed for the first row only.

The case when column_name = ... case expression is used to ensure the columns are output in the correct order (item_number, article and quantity) (both within the row_number() analytic function and in the overall results).

---

ETA: If you could have multiple item_numbers per shipper id, then a couple of tweaks need to be made to the above query:

WITH sample_data AS (SELECT 1 shipper_id, 10 item_number, 'abc' article, 100 quantity FROM dual UNION ALL
                     SELECT 1 shipper_id, 11 item_number, 'xyz' article, 110 quantity FROM dual UNION ALL
                     SELECT 2 shipper_id, 20 item_number, 'efg' article, 200 quantity FROM dual UNION ALL
                     SELECT 3 shipper_id, 30 item_number, 'hij' article, 300 quantity FROM dual)
-- end of mimicking a table called sample_data with data in it
-- see SQL below:
SELECT CASE WHEN row_number() OVER (PARTITION BY shid ORDER BY CASE WHEN column_name = 'Item No.:' THEN 1 WHEN column_name = 'Article:' THEN 2 WHEN column_name = 'Quantity:' THEN 3 ELSE 4 END) = 1
                 THEN shid
       END shipper_id,
       column_name||' '||vals results
FROM   (SELECT shipper_id shid,
               item_number,
               to_char(item_number) "Item No.:",
               article "Article:",
               to_char(quantity) "Quantity:"
        FROM   sample_data)
UNPIVOT (vals FOR column_name IN ("Item No.:", "Article:", "Quantity:"))
ORDER BY shid,
         item_number,
         CASE WHEN column_name = 'Item No.:' THEN 1 WHEN column_name = 'Article:' THEN 2 WHEN column_name = 'Quantity:' THEN 3 ELSE 4 END;

SHIPPER_ID RESULTS
---------- --------------------------------------------------
         1 Item No.: 10
           Article: abc
           Quantity: 100
           Item No.: 11
           Article: xyz
           Quantity: 110
         2 Item No.: 20
           Article: efg
           Quantity: 200
         3 Item No.: 30
           Article: hij
           Quantity: 300