11
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;

enter image description here

How do I get a line break between items ?

gotqn
  • 42,737
  • 46
  • 157
  • 243
Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
  • 1
    *"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)`. – Thom A Dec 11 '19 at 10:34
  • 4
    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. – Salman A Dec 11 '19 at 10:34
  • `STRING_AGG(item, CHAR(10))` for linux style? Or `STRING_AGG(item, CHAR(13)+CHAR(10))` for windows style? – LukStorms Dec 11 '19 at 10:39
  • @Salman: indeed thx, but oddly the string appears truncated. – Ludovic Aubert Dec 11 '19 at 10:44
  • SSMS also truncates varchar data to around x chars (opens SSMS settings) – Salman A Dec 11 '19 at 10:45
  • By default results to `text` are limited to 256 characters per value. For Grid results, CR\LF characters are removed by default **on Copy**. – Thom A Dec 11 '19 at 10:53

4 Answers4

12

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

enter image description here

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.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 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. – Ludovic Aubert Dec 11 '19 at 12:30
  • 4
    @LudovicAubert Restart the studio. – gotqn Dec 11 '19 at 12:32
  • 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 ! – Ludovic Aubert Dec 11 '19 at 12:50
3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    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. – Thom A Dec 11 '19 at 11:03
  • You have no idea how much this helped me! Thank you very much!! – André Rodrigues de Sousa Feb 10 '22 at 13:46
1

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;

user12517870
  • 111
  • 1
  • 4
0

You can use print function for print all output as text

DECLARE @Res  NVARCHAR(MAX)  = (SELECT STRING_AGG(item, '
') FROM items);
print(@Res)
Zanyar Jalal
  • 1,407
  • 12
  • 29