Sql-server – ‘Ungroup’ data based on column

set-returning-functionssql serversql-server-2012

I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has to be in a specific format, but there is one catch: there can be multiple PseudoIds in the same row, separated by a semi-colon.

I have already figured out how to write the LIKE statement for 1 PseudoId, but I have some trouble to write the following query: retrieve all the Id's of A where all the PseudoIds (can be 1 or more) are in the correct format. So for 1, this is easy, but for more a little more tricky.

What I thought I had to was 'ungroup' my table A so that I return a new table A' where the following applies: if there is only 1 PseudoId on a row, then just return the row. Else, split the row on semi-colon and return the rows, which will all be identical but have a different PseudoId.

So for example:

ID | PseudoId  
-------------  
 1 | ABC  
 2 | DEF  
 3 | GHI;JKL  
 4 | MNO  
 5 | PQR;STU;VWX

'Ungrouping' this should yield:

ID | PseudoId  
-------------  
 1 | ABC  
 2 | DEF  
 3 | GHI  
 3 | JKL  
 4 | MNO  
 5 | PQR  
 5 | STU  
 5 | VWX

I would probably use a table-valued function, but not entirely sure how to accomplish this… Much appreciated.

Best Answer

You can do this with a TVF, when you migrate to SQL Server 2016 or higher you can use STRING_SPLIT.

Function source : https://stackoverflow.com/questions/10914576/t-sql-split-string

(slightly modified to use ';' instead of ',' )

CREATE FUNCTION

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

TEST DATA

CREATE TABLE test(id int, pseudoid varchar(255))
insert into test 
values(1 ,'ABC'),
(2 ,'DEF'),
(3 ,'GHI;JKL'),
(4, 'MNO'),
(5 ,'PQR;STU;VWX')

TEST

   select id , s.Name as PseudoID from
    test 
    cross apply dbo.splitstring(pseudoid) as s

RESULT

id  PseudoID 
1   ABC
2   DEF
3   GHI
3   JKL
4   MNO
5   PQR
5   STU
5   VWX
Related Question