'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 ?

sqlsql-servertsqlstring-aggregation


Solution 1:[1]

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.

Solution 2:[2]

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://docs.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;

Solution 3:[3]

You can use print function for print all output as text

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 user12517870
Solution 3 Zanyar J.Ahmed

Related Questions