Search code examples
sqlsql-servert-sqlstring-aggregation

STRING_AGG with line break

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 ?

Solution

  • 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.