Tsql Comma Separation

Comma separated results in SQL

Update (As suggested by @Aaron in the comment)

STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.

SELECT
    STUDENTNUMBER
    , STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
    , STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
FROM Education E
GROUP BY E.STUDENTNUMBER;

Original Answer:

Use FOR XML PATH('') - which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result

SELECT
    STUFF((SELECT ',' + INSTITUTIONNAME
              FROM EDUCATION EE
              WHERE  EE.STUDENTNUMBER = E.STUDENTNUMBER
              ORDER BY sortOrder
              FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)')
        , 1, LEN(','), '') AS XmlPathList
FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE showing results for both STRING_AGG and FOR XML PATH('').

How do I create a comma-separated list using a SQL query?

There is no way to do it in a DB-agnostic way.
So you need to get the whole data-set like this:

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

And then concat the AppName programmatically while grouping by ResName.

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

In SQL, how to convert a column of a comma separated key string to a comma separated value string

Next time you would need to provide ##1-4.
And learn from this answer what it means, i.e. a minimal reproducible example.
You copy it to SSMS and launch it there.

Here is how to implement it in SQL Server 2016:

• STRING_SPLIT() to break it down, one AssignedTo per row.
• SELECT ... FOR XML ... to revert it back to one row for each task.

SQL

-- DDL and sample data population, start
DECLARE @tblA TABLE (TaskID INT PRIMARY KEY, TaskName VARCHAR(100), AssignedTo VARCHAR(30));
INSERT INTO @tblA (TaskID, TaskName, AssignedTo) VALUES
(1, 'Task 1', '1,4'),
(2, 'Task 2', '3'),
(3, 'Task 3', '2,3'),
(4, 'Task 4', '2,4,5');

DECLARE @tblB TABLE (AssigneeID INT PRIMARY KEY, [Name] VARCHAR(30));
INSERT INTO @tblB (AssigneeID, [Name]) VALUES
(1, 'John Smith'),
(2, 'Janet Wright'),
(3, 'Tom Morgan'),
(4, 'Kevin Warren'),
(5, 'Mike Taylor')
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH cte AS
(
    SELECT * FROM @tblA
        CROSS APPLY (SELECT value FROM STRING_SPLIT(AssignedTo, @separator)) AS x
        INNER JOIN @tblB AS b ON x.value = b.AssigneeID
)
SELECT p.TaskID, p.TaskName
    , STUFF((SELECT DISTINCT
                     CONCAT(@separator, c.Name)
                     FROM cte AS c
                     WHERE c.TaskID = p.TaskID
                     FOR XML PATH ('')),
             1, 1, '') AS NameAssignedTo
FROM cte AS p
GROUP BY p.TaskID, p.TaskName;

Output

+--------+----------+---------------------------------------+
| TaskID | TaskName |            NameAssignedTo             |
+--------+----------+---------------------------------------+
|      1 | Task 1   | John Smith,Kevin Warren               |
|      2 | Task 2   | Tom Morgan                            |
|      3 | Task 3   | Janet Wright,Tom Morgan               |
|      4 | Task 4   | Janet Wright,Kevin Warren,Mike Taylor |
+--------+----------+---------------------------------------+

Split comma separated string table row into separate rows using TSQL

Alternatively, you could use XML like so:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e,f,g');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)

Multiple rows to one comma-separated value in Sql Server

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);

SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

Convert multiple rows into one with comma as separator

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

---

Related Topics