Split string to join to table and rejoin

Question

I have data in a database that I cannot change which is stored like this:

DocId	Staff/Relationship
127866	1395/3003,1399/1388

where:

1395/3003 = Cat Stevens/Therapist

and

1399/1388 = Dog Stevens/Program Staff

I only want a concatenated row of the staff names separated by commas like this:

DocId	Staff
127866	Cat Stevens, Dog Stevens

I need to split the ids from the '/' and then join them to a StaffContacts table to get the name. After that, I need to join them again into one row. I am using SQL Server version 13 so cannot use String_Split. I have this so far:

WITH CTE                        
AS                          
  (SELECT                           
  DocId                         
  ,Split.a.value('. ', 'VARCHAR(100)') 'Staff'
   /* Separate into separate columns */                         
  FROM                              
    (SELECT DocId, CAST ('<M>' + REPLACE(Staff, ',', '</M><M>') + '</M>' AS XML) AS Data
     FROM CustomerDocument cd                                               
    ) AS B                      
    CROSS APPLY Data.nodes ('/M') AS Split(a) /* Unpivot into rows so can join to tables below */   
)   


/* Get Staff */                         
(SELECT DISTINCT stafftab.DocId                             
  ,[Staff] = STUFF(/* This is added to delete the leading ', ' */                           
     (SELECT DISTINCT ', '  + ([FirstName]+ ' ' + [LastName])                   
        FROM cte                    
        JOIN Documents d on cte.DocId = d.DocId                 
        JOIN StaffContacts sc ON d.ClientId = sc.ClientId                                   
    WHERE sc.Relationship in (                  
            SELECT CodeId               
            FROM Codes co 
            Where categorycode = 'Staff')       
            AND cte.DocId = stafftab.DocId              
     FOR XML PATH ('')) /* Concatenate multiple rows of data into comma separated values back into one column */                        
     , 1, 2, '')   /* At 1st character, delete 2 which deletes the leading ', ' */                      
         FROM cte stafftab
)

However, I am getting this result:

DocId	Staff
127866	NULL

I tried to include this code in the above since it separates the name from the '/' but I haven't been able to get it to separate successfully to join to the StaffContacts table and then be concatenated again.

SELECT DocId,
LEFT(Staff, charindex('/', Staff)-1)
FROM CustomerDocument cd

DocId	Staff
127866	1395

Is there another way to do this in the version of SSMS that I have? Thank you.

SSMS is just an IDE (like) application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? Tagging [ssms] for your data engine is like tagging [visual-studio] for your programming language; it doesn't actually really tell us anything. — Thom A
– Thom A ♦, Commented May 6 at 7:36
Not to mention SSMS 2014 was replaced by SSMS 16 back in 2016, and SSMS 20 was released last year; you should really be using a more recent version. — Thom A
– Thom A ♦, Commented May 6 at 7:38
Please edit your question to include the version of SQL Server you're working with, and DDL + DML statements to create the sample data. — Zohar Peled
– Zohar Peled, Commented May 7 at 12:10
I had already updated the version I am working with--SQL Server 13. I also don't have control of the version I am working with. I thought I included the DML but I am obviously not proficient enough to know. I will try to delete the question since it sounds like I can't be helped. — WixLove
– WixLove, Commented May 7 at 18:09
Version 13 is known as SQL Server 2016, and if compatibility level is set to 130, you should be able to use the built in STRING_SPLIT method for that. — Zohar Peled
– Zohar Peled, Commented May 8 at 8:09

Zohar Peled · Accepted Answer · 2025-05-08 08:47:14Z

This should work on SQL Server 2016 with compatibility level 130. If your compatibility level is lower, you will need to use some user-defined way to split your strings, I suggest Jeff Moden's CSV Splitter

First, create and populate sample tables (Please save us this step in your future questions):

CREATE TABLE CustomerDocument (
    DocId INT PRIMARY KEY,
    StaffRelationship NVARCHAR(MAX)
);

INSERT INTO CustomerDocument (DocId, StaffRelationship)
VALUES (127866, '1395/3003,1399/1388');


CREATE TABLE Staff
(
    id int,
    name nvarchar(100)
);

INSERT INTO Staff (id, name) VALUES (1395, 'Cat Stevens'), (1399, 'Dog Stevens');

Then, use a CTE combined with STRING_SPLIT and a couple of CROSS APPLIES to get your data in a normalized way, and then use a combination of STUFF and FOR XML PATH to aggregate the strings back together. (unfortunately, STRING_AGG is only available from 2017 version)

WITH CTE AS (
SELECT docid, staffId
FROM CustomerDocument
CROSS APPLY (SELECT * FROM STRING_SPLIT(StaffRelationship, ',', 1)) AS pairs
CROSS APPLY (SELECT value as staffId FROM STRING_SPLIT(pairs.value, '/', 1) WHERE ordinal = 1) AS singles
)

SELECT DISTINCT docid, STUFF
    (
        (
          SELECT ', ' + [name]
          FROM CTE
          INNER JOIN Staff
          ON staffId = id
          FOR XML PATH('')
        )
    , 1, 1, '') AS Staff
FROM CTE

Results:

docid	staff
127866	Cat Stevens, Dog Stevens

Shayen Thomas · Accepted Answer · 2025-05-08 08:09:11Z

-2

1395/3003 = Cat Stevens/Therapist

try splitting it using commas and then join them

answered May 8 at 8:09

Shayen Thomas

11 bronze badge

1 Comment

Sean Lange May 10 at 6:11

This is barely a comment, let alone an answer.

Collectives™ on Stack Overflow

Split string to join to table and rejoin

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related