Comma separated values of multiple columns in single column in sql server

Question

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

Id	Errorcode	ErrorDescription
1	204,201,33	Invalid Object,Out Of Range,Invalid Format
2	21,44	FileInvalid,Invalid date
3	20	Invalid parse

Required Output:

Id	Error
1	204-Invalid Object, 201-Out Of Range, 33-Invalid Format
2	21-FileInvalid, 44-Invalid date
3	20-Invalid parse

I strongly recommend you fix your design. That is the real solution here. — Thom A
– Thom A ♦, Commented Sep 28, 2021 at 9:59
Agree with the above comment. Get each CSV data point onto a separate record, and make your life easier. Even if someone gives you an actual query here, it will likely have terrible performance and will be difficult to maintain. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 28, 2021 at 10:03

lptr · Accepted Answer · 2021-09-28 13:39:29Z

2

..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;

edited Sep 28, 2021 at 13:39

answered Sep 28, 2021 at 10:49

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zhorov Over a year ago

I like this JSON-based approach (+1 from my side) . Two notes: 1) This answer really needs some explanations and 2) STRING_AGG() needs its WITHIN GROUP (ORDER BY CONVERT(int, c.[key])) clause to get ordered results.

Imran Kadri Over a year ago

STRING_AGG() does not support in Sql Server 2016, substitute for STRING_AGG in SQL 2016

lptr Over a year ago

@ImranKadri … for sqlserver 2016 you could use for xml instead of string_agg()

Collectives™ on Stack Overflow

Comma separated values of multiple columns in single column in sql server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related