SQL - Dynamic SQL query to convert single column string delimited with semicolon (;) to multiple columns

Question

Dynamic SQL query to convert single column string delimited with semicolon (;) to multiple columns.

ID           Rollcode            Rack
AA0001   99203; S9088        1350; 1350
ABB0001  99203; S9088        4123; 4123
ADA000   99203; S9088        530; 530
ADM000   99202; S9088;J2308  4516; 4516
ABD000   99203; S9088        3025; 3025

Desired Result should be:

ID    Rollcode1 Rollcode2 Rollcode3 Rack1   Rack2
AA0001  99203   S9088      Null     1350    1350
ABB0001 99203   S9088      Null     4123    4123
ADA000  99203   S9088      Null     530     530
ADM000  99202   S9088     J2308     4516    4516
ABD000  99203   S9088      Null     3025    3025

I have tried for 1 column i.e. (Roll Code) can we make it for other columns

enter code here DECLARE @pivot varchar(8000) 
DECLARE @select varchar(8000) 
SELECT @pivot=coalesce(@pivot+',','')'[Rollcode'+cast`
(number+1 as varchar(10))+']'
FROM master..spt_values where type='p' and 
number<=(SELECT max(len(Rollcode)-len(replace
(Rollcode,';',''))) FROM tablename) 
SELECT @select=' select p.* 
from ( select  ID,substring(Rollcode, start+2, endPos-Start-2) as token, 
''Rollcode''+cast(row_number() over(partition by ID order by start) as 
varchar(10)) as n 
 from ( select ID, Rollcode, n as start
            , charindex('';'',Rollcode,n+2) endPos 
  from (select number as n from master..spt_values where type=''p'') num 
  cross join  
  ( 
  select  
  ID, '';'' + Rollcode +'';'' as Rollcode  
  from  tablename 
  ) m 
  where n < len(Rollcode)-1 
  and substring(Rollcode,n+1,1) = '';'') as Rollcode 
  ) pvt 
  Pivot ( max(token)for n in ('+@pivot+'))p'
  EXEC(@select)  

Answer 1

Try This

declare @roll table (ID nvarchar(20),RollCode nvarchar(100),Rack Nvarchar(100))

insert into @roll
              select 'AA0001'   ,'99203; S9088'         ,'1350; 1350'
union all select 'ABB0001'  ,'99203; S9088'         ,'4123; 4123'
union all select 'ADA000'   ,'99203; S9088'         ,'530; 530'
union all select 'ADM000'   ,'99202; S9088;J2308'   ,'4516; 4516'
union all select 'ABD000'   ,'99203; S9088'         ,'3025; 3025'


 select ID, 
    PARSENAME(replace(RollCode,';','.'),1) as 'RollCode3', 
    PARSENAME(replace(RollCode,';','.'),2) as 'RollCode2',
    PARSENAME(replace(RollCode,';','.'),3) as 'RollCode1',
    PARSENAME(replace(Rack,';','.'),1) as 'Rack2',
    PARSENAME(replace(Rack,';','.'),2) as 'Rack1'

from @roll

Answer 2

First of all you need a way to split the values separated with semi colons. There are various of split functions (some written in pure T-SQL and some in .net).

I am using a CLR function to solve your issue. It's named [dbo].[fn_Utils_RegexSplitWithOrder] and splits values by regex pattern. Here is the .net code:

[SqlFunction(FillRowMethodName = "FillRowForSplitWithOrder")]
public static IEnumerable SplitWithOrder(SqlString sqlInput, SqlString sqlPattern)
{
    string[] substrings;
    List<Tuple<SqlInt64, SqlString>> values = new List<Tuple<SqlInt64, SqlString>>(); ;

    if (sqlInput.IsNull || sqlPattern.IsNull)
    {
        substrings = new string[0];
    }
    else
    {
        substrings = Regex.Split(sqlInput.Value, sqlPattern.Value);
    }

    for (int index = 0; index < substrings.Length; index++)
    {
        values.Add(new Tuple<SqlInt64, SqlString>(new SqlInt64(index), new SqlString(substrings[index])));
    }

    return values;
}

You can check this answer in order to learn how CLR functions are deployed. You can implement pure T-SQL function for splitting if you want.

Now, we have a function to split the data (and we need to store it):

DECLARE @DataSource TABLE
(
    [ID] VARCHAR(12)
   ,[RollCode] VARCHAR(MAX)
   ,[Rack] VARCHAR(MAX)
);

INSERT INTO @DataSource ([ID], [RollCode], [Rack])
VALUES ('AA0001', '99203; S9088', '1350; 1350')
      ,('ABB0001', '99203; S9088', '4123; 4123')
      ,('ADA000', '99203; S9088', '530; 530')
      ,('ADM000', '99202; S9088;J2308', '4516; 4516')
      ,('ABD000', '99203; S9088', '3025; 3025');

IF OBJECT_ID('tempdb..#TempDataSource') IS NOT NULL
BEGIN
    DROP TABLE #TempDataSource;
END;

CREATE TABLE #TempDataSource
(
    [ID] VARCHAR(12)
   ,[Type] VARCHAR(8)
   ,[Index] INT
   ,[Value] VARCHAR(12)
);

INSERT INTO #TempDataSource ([ID], [Type], [Index], [Value])
SELECT DS.[ID]
      ,'Rollcode'
      ,RS.[index]
      ,LTRIM(RTRIM(RS.[value]))
FROM @DataSource DS
CROSS APPLY [dbo].[fn_Utils_RegexSplitWithOrder] (DS.[RollCode], ';') RS
UNION ALL
SELECT DS.[ID]
      ,'Rack'
      ,RS.[index]
      ,LTRIM(RTRIM(RS.[value]))
FROM @DataSource DS
CROSS APPLY [dbo].[fn_Utils_RegexSplitWithOrder] (DS.[Rack], ';') RS

In the table you have:

and we are now ready for dynamic PIVOT. Note, I am ordering the columns like you want but you can change how the [ColumnOrder] is calculated:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
DECLARE @DynamicPIVOTColumns NVARCHAR(MAX);

SELECT @DynamicPIVOTColumns = STUFF
(
    (
        SELECT ',[' + [ColumnName] + ']'
        FROM
        (
            SELECT DISTINCT CONCAT([Type], [Index])
                  ,[Index] + IIF([Type] = 'RACK', 100000, 0)
            FROM #TempDataSource
        ) DS ([ColumnName], [ColumnOrder])
        ORDER BY [ColumnOrder]
        FOR XML PATH('')
    )
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement =
'
SELECT *
FROM
(
    SELECT [ID]
          ,CONCAT([Type], [Index])
          ,[Value]
    FROM #TempDataSource
) DS ([ID], [ColumnName], [Value])
PIVOT
(
    MAX([Value]) FOR [ColumnName] IN (' + @DynamicPIVOTColumns + ')
) PVT
';

EXEC  sp_executesql @DynamicTSQLStatement;

The last part gives you:

It may looks complicated, but it is not. It's trivial dynamic PIVOT task. Just implement function for splitting the values and you are OK.

Answer 3

Hint try this,

SELECT LEFT(RoleCode,CHARINDEX(';',RoleCode)-1) AS RoleCode1,
       RIGHT(RoleCode,CHARINDEX(';',RoleCode)) AS RoleCode2
FROM tableName