STRING_AGG with line break

Question

DROP TABLE IF EXISTS items;
CREATE TABLE items (item varchar(20));
INSERT INTO items VALUES ('apple'),('raspberry');
SELECT STRING_AGG(item, CHAR(13)) AS item_list FROM items;

How do I get a line break between items ?

Emirate

"How do I get a line break between items ?" CHAR(13) is Carriage Return. CHAR(10) in a Line Feed. Change CHAR(13) to CHAR(10)? If you want both characters (to be explicit) use CHAR(13) + CHAR(10). – Villenage

The char(13) character is there... you just cannot see it in grid view. Click Query > Results to > Results to text from SSMS menu to view result in text mode. – Fink

STRING_AGG(item, CHAR(10)) for linux style? Or STRING_AGG(item, CHAR(13)+CHAR(10)) for windows style? – Katti

@Salman: indeed thx, but oddly the string appears truncated. – Emirate

SSMS also truncates varchar data to around x chars (opens SSMS settings) – Fink

By default results to text are limited to 256 characters per value. For Grid results, CR\LF characters are removed by default on Copy. – Villenage

Answer 1

Your query is working fine on my environment. You need to enable this settings in the management studio:

Tools > Options > Query Results > Results to Grid

It makes no sense for me why, but they have changed the default behavior several SSMS releases ago.

Syrinx

Thx, good to know ! But even with CHAR(13)+CHAR(10), and after doing what you are suggesting, there is no line break in the grid. And even if I copy/paste from the result grid into a text editor, same result. I am running SSMS under windows. – Emirate

@LudovicAubert Restart the studio. – Syrinx

This time, there are invisible line breaks in the grid. If I copy/paste from the grid into a text editor, they become visible in the editor. Thx ! – Emirate

Answer 2

Just put it in the string:

SELECT STRING_AGG(item, '
') AS item_list
FROM items;

One caveat is that the definition of "end of line" depends on the operating system. So, this will insert a different value on Unix versus Windows.

Here is a db<>fiddle.

Tube

This is no different to what the OP already has, apart from it is likely both a carriage return and line feed (depending on the environment you typed the above). The "problem" very likely stems from the fact the OP has the "Retain CR/LF on copy or save" option switched off in SSMS. – Villenage

You have no idea how much this helped me! Thank you very much!! – Compotation

Answer 3

If using the Management Studio Query Editor, the Results to Grid option cannot implement the carriage return. Switch to Results to Text to see the result set properly.

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

SELECT STRING_AGG(item, CHAR(13)) AS item_list FROM items;

Sepulchre

Answer 4

You can use print function for print all output as text

DECLARE @Res  NVARCHAR(MAX)  = (SELECT STRING_AGG(item, '
') FROM items);
print(@Res)

Niggling