Turning SQL table column into sentence structure

Question

I need to be able to take a column from a SQL table and write a query that will output this in a sentence format. example

column A
---------
Abraham
Jones
Henry
Walter

output would look like this

Abraham, Jones, Henry, Walter

This is called string aggregation and is probably supported by whatever database you are using. You can look up the functionality for your database, because the syntax varies by database. — Gordon Linoff, Apr 22 '20 at 19:31
After 1 hour without clarification on the database, I'm voting to close. — The Impaler, Apr 22 '20 at 20:40

hmr2020 · Answer 1 · 2020-04-23T14:45:25.577

0

If you are using SQL Server 2017 or above:

DECLARE @Table TABLE( ColumnName VARCHAR(25))
INSERT INTO @Table VALUES ('Abraham'),('Jones'),('Henry'),('Walter')

SELECT STRING_AGG(ColumnName,', ') sentence
FROM @Table

For SQL Server versions below 2017, use the For Xml Path approach:

SELECT  STUFF((SELECT ', ' + ColumnName 
              FROM @Table t1
              FOR XML PATH (''))
             , 1, 1, '')

This Stackoverflow post explains more. Good luck!

edited Apr 23 '20 at 14:45

answered Apr 22 '20 at 19:50

hmr2020

1
3

Thanks, but I get an error(see below) and not sure why. I'm trying to run this from SSMS v17.9, so I would think the function would work. It even gave me it to select as an option. 'STRING_AGG' is not a recognized built-in function name. – Jeff Rohrig Apr 23 '20 at 12:50
STRING_AGG is for SQL Server 2017 and later. I believe your SQL Server version is below 2017 even if you have a newer SSMS version. Try running "SELECT @@VERSION" to see what your version is. – hmr2020 Apr 23 '20 at 14:14
See modified answer above. Good luck! – hmr2020 Apr 23 '20 at 14:46

Turning SQL table column into sentence structure

1 Answers1