Menu
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 ?
743
-- Using both \r\n SELECT 'First line. \r\nSecond Line. ' AS 'New Line'; -- Using both \n SELECT 'First line.
STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.
In SQL Server, we can use the CHAR function with ASCII number code. We can use the following ASCII codes in SQL Server: Char(10) – New Line / Line Break. Char(13) – Carriage Return.
Remove and Replace Carriage Returns and Line Breaks in SQL Using SQL to remove a line feed or carriage return means using the CHAR function. A line feed is CHAR(10); a carriage return is CHAR(13).
If the empty string '' is specified in end, a line break will not occur at the end. Any string can be specified in end. However, if you want to concatenate the character strings and output, it is easier to concatenate the original character strings.
This article describes how to handle strings including line breaks (line feeds, new lines) in Python. Split a string into a list by line breaks: splitlines () Inserting a newline code , into a string will result in a line break at that location.
The string method splitlines () can be used to split a string by line breaks into a list. In addition to and , it is also splitted by \v (line tabulation) or \f (form feed), etc. See also the following article for more information on splitlines ().
If input argument is string type ( NVARCHAR, VARCHAR ), result type will be same as input type. The following table lists automatic conversions: STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string.
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.
157
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.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
