Comma separated for multiple items not working properly for SQL Sever 2012

Question

I am using SQL Server 2012 (v11.0.5058.0 - X64). I want to perform comma separated for multiple columns. Since my SQL Server version is 2012 I can't use string_agg or ListAgg so I am trying with a CTE and FOR XML PATH which is new for me.

Output Explanation:

Supposed If One staff ID Contain multiple Cost_Center then it should comma separated for respective Staff_ID

Current table:

Staff_No |  Name  | CostCenter     |  status  |
---------+--------+----------------+----------+
1270     | WASI   | 850110 - CPP   |   Active |
1345     | FAK    | 124600 - CPP   |   Active |
1270     | WASI   | 850870 - BKR   |   Active |

Desired output:

Staff_No |  Name  | CostCenter                   |  status  |
---------+--------+------------------------------+----------+
1270     | WASI   | 850110 - CPP , 850870 - BKR  |   Active |
1345     | FAK    | 124600 - CPP                 |   Active |

I tried this code:

WITH CTE_TableName AS 
(
    SELECT   
        a.Staff_No, a.Name,  
        CONVERT(varchar(19), a.COST_CENTER_CODE) + ' - ' + b.COST_CENTER_DESC  as CostCenter,   
        CASE a.Active 
           WHEN 1 THEN 'Active' 
           WHEN 2 THEN 'Inactive' 
        END AS status   
   FROM
       [FAV_VS_STAFF_M] a, [FAV_VS_COST_CENTER_M] b 
)
SELECT 
    t0.Staff_No, t0.Name, t0.status,
    STUFF((SELECT ',' + t2.CostCenter
           FROM CTE_TableName t2
           WHERE t2.Staff_No = t0.Staff_No
           ORDER BY t2.CostCenter
           FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
FROM 
    CTE_TableName t0
GROUP BY 
    t0.Staff_No , t0.Name, t0.status
ORDER BY 
    Staff_No;

I am not getting the desired output. Where I am going wrong? Please help me

@Larnu I am getting all `Cost_Center` Data with comma separated but i need only those data which are multiple — ak74, Apr 05 '21 at 10:14
@Charlieface I used but i am getting `ORDER BY items must appear in the select list if SELECT DISTINCT is specified.` error — ak74, Apr 05 '21 at 10:20
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged — marc_s, Apr 05 '21 at 10:21
And also: **Bad habits to kick** - [using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) — marc_s, Apr 05 '21 at 10:21
Either use `ORDER BY 1` to order by the first (and only) column, or repeat the full `select` in the `ORDER BY` — Charlieface, Apr 05 '21 at 10:38
I'm lost. You show one table. But your query references more than one table. What does the data really look like? — Gordon Linoff, Apr 05 '21 at 10:39
@GordonLinoff Table is just for sample output references should i post original table here ? — ak74, Apr 05 '21 at 10:44

Serg · Accepted Answer · 2021-04-05T14:31:50.850

As a rule, a WHERE clause of SELECT .. FOR XML .. shoud make the subquery depend on exactly the same columns which are listed in the GROUP BY clause of the outer query. Try

WITH CTE_TableName AS (
        SELECT   a.Staff_No , a.Name , 
        CONVERT(varchar(19), a.COST_CENTER_CODE) + ' - ' + b.COST_CENTER_DESC  as CostCenter,   
CASE a.Active WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END AS status   
from [FAV_VS_STAFF_M] a, [FAV_VS_COST_CENTER_M] b )
SELECT t0.Staff_No, t0.Name , t0.status
     , STUFF((
       SELECT ',' + t2.CostCenter
         FROM CTE_TableName t2
        WHERE t2.Staff_No = t0.Staff_No
            AND t2.Name = t0.Name AND t2.status = t0.status
        ORDER BY t2.CostCenter
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
  GROUP BY t0.Staff_No , t0.Name, t0.status
  ORDER BY Staff_No;

db<>fiddle

As i am getting all multiple `Cost_Center` i am looking if `Staff_ID` as multiple `Cost_Center` then it should separated with comma. Kindly Check My updated question — ak74, Apr 05 '21 at 10:18

Charlieface · Answer 2 · 2021-04-05T13:13:13.883

You need a DISTINCT or GROUP BY clause in your inner SELECT if you only want distinct values. There are a number of ways to do this, the simplest is:

    STUFF((SELECT DISTINCT ',' + t2.CostCenter
           FROM CTE_TableName t2
           WHERE t2.Staff_No = t0.Staff_No
             AND t2.Name = t0.Name AND t2.status = t0.status
           ORDER BY 1
           FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
    , 1, LEN(','), '') AS FieldBs

Note the addition of TYPE and value('text()[1]','nvarchar(max)') to ensure the XML is unescaped properly.

As noted by @Serg, you also need to correlate the join properly AND t2.Name = t0.Name AND t2.status = t0.status

Another way to skin this cat:

    STUFF((SELECT DISTINCT ',' + t2.CostCenter
           FROM CTE_TableName t2
           WHERE t2.Staff_No = t0.Staff_No
             AND t2.Name = t0.Name AND t2.status = t0.status
           ORDER BY ',' + t2.CostCenter
           FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
    , 1, LEN(','), '') AS FieldBs

Or:

    STUFF((SELECT ',' + t2.CostCenter
           FROM CTE_TableName t2
           WHERE t2.Staff_No = t0.Staff_No
             AND t2.Name = t0.Name AND t2.status = t0.status
           GROUP BY ',' + t2.CostCenter
           ORDER BY ',' + t2.CostCenter
           FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
    , 1, LEN(','), '') AS FieldBs

See this question for why you need to specify the ORDER BY like this.

I tried as you suggested but i am getting error like `Cannot call methods on nvarchar(max).` — ak74, Apr 05 '21 at 10:50
I tried all three ways but same output i am getting repeating for all `Cost_Center` . — ak74, Apr 05 '21 at 11:12
Serg is right you need to correlate the apply. have modified — Charlieface, Apr 05 '21 at 13:13

Comma separated for multiple items not working properly for SQL Sever 2012

2 Answers2