Concatenate Prefix based on ID -TSQL

Question

What i want is to manage to concatenate all Prefixs with the same ID.

Here is the code i use:

  CTE_1 (ID,Prefix)AS (SELECT *, STUFF((
            SELECT DISTINCT ', ' + Prefix
            FROM CTE
            WHERE ID = t.ID
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT ID
    FROM CTE
) t)

The code works fine but when the Prefix is empty it import a , and i wan to avoid it.

Any idea?

SQL version: Microsoft SQL Server 2014

Answer 1

You can fix your query as follow:

CTE_1 (ID,Prefix)AS (SELECT *, STUFF((
        SELECT DISTINCT 
             CASE 
                 WHEN Prefix IS NOT NULL AND Prefix <> ''
                 THEN ', ' + Prefix
                 ELSE ''
             END
        FROM CTE
        WHERE ID = t.ID
        FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT ID
    FROM CTE
) t)