I have a table with three column Id, Errorcode, ErrorDescription in which errorcode and errordescription columns have comma separated value in it

Here i need to concatenate the value of column 2 and 3, such as first value of column 2 - first value of column 3 and so on with comma(,) seprated

Example: Actual Table

Required Output:

CodePudding user response：

..from sqlserver 2017 ..fiddle

select *
from
(
values
(1, '204,201,33', 'Invalid Object,Out Of Range,Invalid Format'),
(2, '21,44', 'FileInvalid,Invalid date'),
(3, '20', 'Invalid parse')
) as t(Id, Errorcode, ErrorDescription)
cross apply
(
select string_agg(c.value '-' e.value, ', ') within group (order by cast(c.[key] as int)) as error
from openjson('["' replace(string_escape(t.Errorcode, 'json'), ',', '","') '"]') as c
join openjson('["' replace(string_escape(t.ErrorDescription, 'json'), ',', '","') '"]') as e on c.[key] = e.[key]
) as e;

..sqlserver 2016..fiddle

select *
from
(
values
(1, '204,201,33', 'Invalid Object,Out Of Range,Invalid Format'),
(2, '21,44', 'FileInvalid,Invalid date'),
(3, '20', 'Invalid parse')
) as t(Id, Errorcode, ErrorDescription)
cross apply
(
select stuff ((
   select ', ' c.value '-' e.value
   from openjson('["' replace(string_escape(t.Errorcode, 'json'), ',', '","') '"]') as c
   join openjson('["' replace(string_escape(t.ErrorDescription, 'json'), ',', '","') '"]') as e on c.[key] = e.[key]
   order by cast(c.[key] as int)
   for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as error
) as e;