Menu
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
445
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)
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.
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;
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.
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 |
Here's a solution using variables:
DECLARE @out VARCHAR(MAX)
SELECT @out = COALESCE(@out+',' ,'') + name
FROM tblUsers
SELECT @lout
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
