Remove additional comma without knowing the length of the string

Question

My tables

MyTable

+----+-------+---------------+
| Id | Title | DependencyIds |
+----+-------+---------------+

DependentIds contains values like 14;77;120.

---

MyDependentTable

+--------------+------+
| DependencyId | Name |
+--------------+------+

---

Background

I have to select data from MyTable with every dependency from MyDependentTable separated with a comma.

Expected output:

+---------+-------------------------------------+
| Title   | Dependencies                        |
+---------+-------------------------------------+
| Test    | ABC, One-two-three, Some Dependency |
+---------+-------------------------------------+
| Example | ABC                                 |
+---------+-------------------------------------+

---

My query

SELECT t.Title,
    (SELECT ISNULL((
        SELECT DISTINCT
        (
            SELECT dt.Name + '',
                   CASE WHEN DependencyIds LIKE '%;%' THEN ', ' ELSE '' END AS [text()]
            FROM MyDependentTable dt
            WHERE dt.DependencyId IN (SELECT Value FROM dbo.fSplitIds(t.DependencyIds, ';'))
            ORDER BY dt.DependencyId
            FOR XML PATH('')
        )), '')) Dependencies
FROM dbo.MyTable t

---

Problem description

The query works, but adds an additional comma when there are multiple dependencies:

+---------+---------------------------------------+
| Title   | Dependencies                          |
+---------+---------------------------------------+
| Test    | ABC, One-two-three, Some Dependency,  |
+---------+---------------------------------------+
| Example | ABC                                   |
+---------+---------------------------------------+

I can't use SUBSTRING(ISNULL(... because I can't access the length of the string and therefore I'm not able to set the length of the SUBSTRING.

Is there any possibility to get rid of that unnecessary additional comma?

Answer 1

Normally for group concatenation in Sql Server, people will add leading comma and remove it using STUFF function but even that looks ugly.

Outer Apply method looks neat to do this instead of correlated sub-query. In this method we don't have to wrap the SELECT query with ISNULL or STUFF

SELECT DISTINCT t.title, 
                Isnull(LEFT(dependencies, Len(dependencies) - 1), '') 
                Dependencies 
FROM   dbo.mytable t 
       OUTER apply (SELECT dt.NAME + ',' 
                    FROM   mydependenttable dt 
                    WHERE  dt.dependencyid IN (SELECT value 
                                               FROM 
                           dbo.Fsplitids(t.dependencyids,';')) 
                    ORDER  BY dt.dependencyid 
                    FOR xml path('')) ou (dependencies)