Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server select results as string separated with ',' [duplicate]

Tags:

sql-server

I have a select query that returns one column and I want to convert that to string rows separated with ','

Select name 
from tblUsers

Gives a result:

Row1; asieh
Row2:amir
Row3:safoora

I want to return

Row1: asieh,amir,safoora
like image 445
Asieh hojatoleslami Avatar asked Sep 09 '13 16:09

Asieh hojatoleslami


People also ask

How do you split a string into a table in SQL?

Introduction to SQL Server STRING_SPLIT () function The STRING_SPLIT () function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator. The following shows the syntax of the STRING_SPLIT () function: STRING_SPLIT (input_string, separator)

What is the difference between input_string () and separator () in MySQL?

input_string is a character-based expression that evaluates to a string of NVARCHAR, VARCHAR, NCHAR, or CHAR. separator is a single character used as a separator for splitting. The STRING_SPLIT () function returns a single-column table, whose column name is value. This result table contains rows which are the substrings.

How to return the entire row for each duplicate row in SQL?

To return the entire row for each duplicate row, you join the result of the above query with the t1 table using a common table expression ( CTE ): WITH cte AS ( SELECT a, b, COUNT (*) occurrences FROM t1 GROUP BY a, b HAVING COUNT (*) > 1 ) SELECT t1.id, t1.a, t1.b FROM t1 INNER JOIN cte ON cte.a = t1.a AND cte.b = t1.b ORDER BY t1.a, t1.b;

Can I Make my query results appear as a list in SQL?

Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use. While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly.


2 Answers

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE tblUsers
    ([name] varchar(7))
;

INSERT INTO tblUsers
    ([name])
VALUES
    ('asieh'),
    ('amir'),
    ('safoora')
;

Query 1:

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

Results:

|              NAMES |
|--------------------|
| asieh,amir,safoora |
like image 146
Fabien TheSolution Avatar answered Sep 28 '22 08:09

Fabien TheSolution


Here's a solution using variables:

DECLARE @out VARCHAR(MAX)
SELECT @out = COALESCE(@out+',' ,'') + name
FROM tblUsers
SELECT @lout
like image 21
Kermit Avatar answered Sep 28 '22 08:09

Kermit