sql-serverstuff

STUFF function along with charIndex() in sql server

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 :

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.

Solution

  • 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)), ',')

    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;