STUFF function along with charIndex() in sql server

Question

I need to display a table column (assume 2 columns) values with comma-separated in the output

Table t1:

col1    Col2    
================
xyz       ab/cde 
pqr       uv/wxy 
xyz       fg/hij     
lmn       rtg
pqr       12/456

Table t2:

col1        
======
xyz         
pqr   

Output needed:(column col2 to be populated in table2 with following values)

col1    Col2    
================
xyz     ab,fg
pqr     uv,12

Output expected :

• col1 values of t1 should be equal to col1 values of t2.
• For the records passing step1, take col2 values before slash from t1 and have them comma separated for each value of col1.
• EDITED :

How to get only distinct values from col2 of t1 table. Ex: if the values of the table are

col1    Col2    
================
xyz       ab/cde 
pqr       uv/wxy 
xyz       fg/hij     
lmn       fg
pqr       fg/456 tehn output to be only ab,fg,uv...

Can anyone please help me on this?I'm not getting on how to do it in sql server.

Answer 1

Assuming a version that supports STRING_AGG:

UPDATE t2 
  SET t2.col2 = agg.agg
FROM dbo.t2
INNER JOIN 
(
  SELECT col1,
    agg = STRING_AGG(SUBSTRING(col2, 1, 
          NULLIF(charindex('/', col2)-1,-1)), ',')
  FROM dbo.t1
  GROUP BY col1
) AS agg
ON t2.col1 = agg.col1;

If you want the resulting string to contain values from t2.col2 that don't contain slashes (ambiguous given the sample data), change the NULLIF line to:

COALESCE(NULLIF(charindex('/', col2)-1,-1), 255)), ',')

• Examples

And yes, if you want to code golf me, you could change...

SUBSTRING(col2, 1,

...to...

LEFT(col2, 

Edited Answer:

To prevent duplicates, you have to use DISTINCT or GROUP BY on the substring. I avoid duplicating the LEFT/SUBSTRING logic by using a CTE. There are certainly other ways (like CROSS APPLY perhaps).

WITH src AS 
  (
    SELECT col1, pf = LEFT(col2, 
           NULLIF(charindex('/', col2)-1,-1))
    FROM dbo.t1
  ),
  agg AS 
  (
    SELECT col1, agg = STRING_AGG(pf, ',')
    FROM (SELECT col1, pf FROM src GROUP BY col1, pf) AS x
    GROUP BY col1
  )
  UPDATE t2 SET t2.col2 = agg.agg
    FROM dbo.t2
    INNER JOIN agg 
    ON t2.col1 = agg.col1;

• Updated example