SQL Server convert select a column and convert it to a string

Question

Is it possible to write a statement that selects a column from a table and converts the results to a string?

Ideally I would want to have comma separated values.

For example, say that the SELECT statement looks something like

SELECT column
FROM table
WHERE column<10

and the result is a column with values

|column|
--------
|  1   |
|  3   |
|  5   |
|  9   |

I want as a result the string "1, 3, 5, 9"

Answer 1

You can do it like this:

Fiddle demo

declare @results varchar(500)

select @results = coalesce(@results + ',', '') +  convert(varchar(12),col)
from t
order by col

select @results as results

| RESULTS |
-----------
| 1,3,5,9 |

Answer 2

There is new method in SQL Server 2017:

SELECT STRING_AGG (column, ',') AS column FROM Table;

that will produce 1,3,5,9 for you

Answer 3

select  stuff(list,1,1,'')
from    (
        select  ',' + cast(col1 as varchar(16)) as [text()]
        from    YourTable
        for     xml path('')
        ) as Sub(list)

Example at SQL Fiddle.

Answer 4

SELECT  CAST(<COLUMN Name> AS VARCHAR(3)) + ','
FROM    <TABLE Name>
FOR     XML PATH('')

Answer 5

The current accepted answer doesn't work for multiple groupings.
Try this when you need to operate on categories of column row-values.

Suppose I have the following data:

+---------+-----------+
| column1 |  column2  |
+---------+-----------+
| cat     | Felon     |
| cat     | Purz      |
| dog     | Fido      |
| dog     | Beethoven |
| dog     | Buddy     |
| bird    | Tweety    |
+---------+-----------+

And I want this as my output:

+------+----------------------+
| type |        names         |
+------+----------------------+
| cat  | Felon,Purz           |
| dog  | Fido,Beethoven,Buddy |
| bird | Tweety               |
+------+----------------------+

(If you're following along:

create table #column_to_list (column1 varchar(30), column2 varchar(30))
insert into #column_to_list
values 
('cat','Felon'),
('cat','Purz'),
('dog','Fido'),
('dog','Beethoven'),
('dog','Buddy'),
('bird','Tweety')

)

Now – I don’t want to go into all the syntax, but as you can see, this does the initial trick for us:

select ',' + cast(column2 as varchar(255)) as [text()]  
from #column_to_list sub
where column1 = 'dog'
for xml path('')
--Using "as [text()]" here is specific to the “for XML” line after our where clause and we can’t give a name to our selection, hence the weird column_name

output:

+------------------------------------------+
| XML_F52E2B61-18A1-11d1-B105-00805F49916B |
+------------------------------------------+
| ,Fido,Beethoven,Buddy                    |
+------------------------------------------+

You can see it’s limited in that it was for just one grouping (where column1 = ‘dog’) and it left a comma in the front, and additionally it’s named weird.

So, first let's handle the leading comma using the 'stuff' function and name our column stuff_list:

select stuff([list],1,1,'') as stuff_list
from (select ',' + cast(column2 as varchar(255)) as [text()]
         from #column_to_list sub
         where column1 = 'dog'
         for xml path('')
         ) sub_query([list]) 
--"sub_query([list])" just names our column as '[list]' so we can refer to it in the stuff function.  

Output:

+----------------------+
|      stuff_list      |
+----------------------+
| Fido,Beethoven,Buddy |
+----------------------+

Finally let’s just mush this into a select statement, noting the reference to the top_query alias defining which column1 we want (on the 5th line here):

select top_query.column1, 
          (select stuff([list],1,1,'') as stuff_list
         from (select ',' + cast(column2 as varchar(255)) as [text()]
                  from #column_to_list sub
                  where sub.column1 = top_query.column1
                  for xml path('')
                  ) sub_query([list])
              ) as pet_list
from  #column_to_list top_query
group by column1
order by column1

output:

+---------+----------------------+
| column1 |       pet_list       |
+---------+----------------------+
| bird    | Tweety               |
| cat     | Felon,Purz           |
| dog     | Fido,Beethoven,Buddy |
+---------+----------------------+

And we’re done.

You can read more here:

• FOR XML PATH in SQL server and [text()]
• https://learn.microsoft.com/en-us/sql/relational-databases/xml/use-path-mode-with-for-xml?view=sql-server-2017
• https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

Answer 6

This a stab at creating a reusable column to comma separated string. In this case, I only one strings that have values and I do not want empty strings or nulls.

First I create a user defined type that is a one column table.

-- ================================
-- Create User-defined Table Type
-- ================================
USE [RSINET.MVC]
GO

-- Create the data type
CREATE TYPE [dbo].[SingleVarcharColumn] AS TABLE 
(
    data NVARCHAR(max)
)
GO

The real purpose of the type is to simplify creating a scalar function to put the column into comma separated values.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Rob Peterson        
-- Create date: 8-26-2015
-- Description: This will take a single varchar column and convert it to
-- comma separated values.
-- =============================================
CREATE FUNCTION fnGetCommaSeparatedString 
(
    -- Add the parameters for the function here
    @column AS [dbo].[SingleVarcharColumn] READONLY
)
RETURNS VARCHAR(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @result VARCHAR(MAX)
DECLARE @current VARCHAR(MAX)
DECLARE @counter INT
DECLARE @c CURSOR

SET @result = ''
SET @counter = 0
-- Add the T-SQL statements to compute the return value here
SET @c = CURSOR FAST_FORWARD
    FOR SELECT COALESCE(data,'') FROM @column
    OPEN @c
    FETCH NEXT FROM @c
    INTO @current
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @result <> '' AND @current <> '' SET @result = @result + ',' + @current
        IF @result = '' AND @current <> '' SET @result = @current
    FETCH NEXT FROM @c
    INTO @current
    END
    CLOSE @c
    DEALLOCATE @c
-- Return the result of the function
RETURN @result

END
GO

Now, to use this. I select the column I want to convert to a comma separated string into the SingleVarcharColumn Type.

DECLARE @s as SingleVarcharColumn

INSERT INTO @s VALUES ('rob')
INSERT INTO @s VALUES ('paul')
INSERT INTO @s VALUES ('james')
INSERT INTO @s VALUES (null)


INSERT INTO @s
SELECT iClientID FROM [dbo].tClient

SELECT [dbo].fnGetCommaSeparatedString(@s)

To get results like this.

rob,paul,james,1,9,10,11,12,13,14,15,16,18,19,23,26,27,28,29,30,31,32,34,35,36,37,38,39,40,41,42,44,45,46,47,48,49,50,52,53,54,56,57,59,60,61,62,63,64,65,66,67,68,69,70,71,72,74,75,76,77,78,81,82,83,84,87,88,90,91,92,93,94,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,120,121,122,123,124,125,126,127,128,129,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159

I made my data column in my SingleVarcharColumn type an NVARCHAR(MAX) which may hurt performance, but I flexibility was what I was looking for and it runs fast enough for my purposes. It would probably be faster if it were a varchar and if it had a fixed and smaller width, but I have not tested it.

Answer 7

You can use the following method:

select
STUFF(
        (
        select ', ' + CONVERT(varchar(10), ID) FROM @temp
        where ID<50
group by ID for xml path('')
        ), 1, 2, '') as IDs

---

Implementation:

Declare @temp Table(
ID int
)
insert into @temp
(ID)
values
(1)
insert into @temp
(ID)
values
(3)
insert into @temp
(ID)
values
(5)
insert into @temp
(ID)
values
(9)

 select
STUFF(
        (
        select ', ' + CONVERT(varchar(10), ID) FROM @temp
        where ID<50
group by ID for xml path('')
        ), 1, 2, '') as IDs

Result will be:

Answer 8

--------------------------- easy I Found Like it ----------------

SELECT STUFF((
        select ','+ name 
        from tblUsers
        FOR XML PATH('')
        )
        ,1,1,'') AS names

name
---------
mari, joan, carls
---------

Answer 9

ALTER PROCEDURE [dbo].[spConvertir_CampoACadena]( @nomb_tabla   varchar(30),
                          @campo_tabla  varchar(30),
                          @delimitador  varchar(5),
                          @respuesta    varchar(max) OUTPUT
)
AS
DECLARE @query      varchar(1000),
    @cadena     varchar(500)
BEGIN
  SET @query = 'SELECT @cadena  = COALESCE(@cadena + '''+ @delimitador +''', '+ '''''' +') + '+ @campo_tabla + ' FROM '+@nomb_tabla
  --select @query
  EXEC(@query)
  SET @respuesta = @cadena  
END

Answer 10

Use LISTAGG function, ex. SELECT LISTAGG(colmn) FROM table_name;

Answer 11

Use simplest way of doing this-

SELECT GROUP_CONCAT(Column) from table

Answer 12

+------+----------------------+
| type |        names         |
+------+----------------------+
| cat  | Felon                |
| cat  | Purz                 |
| dog  | Fido                 |
| dog  | Beethoven            |
| dog  | Buddy                |
| bird | Tweety               |
+------+----------------------+

---

select group_concat(name) from Pets
group by type

Here you can easily get the answer in single SQL and by using group by in your SQL you can separate the result based on that column value. Also you can use your own custom separator for splitting values

Result:

+------+----------------------+
| type |        names         |
+------+----------------------+
| cat  | Felon,Purz           |
| dog  | Fido,Beethoven,Buddy |
| bird | Tweety               |
+------+----------------------+