Useful sql script for developers

Split array column into rows

Use OPENJSON feature in sql server (2016+) to parse json array string into rows.

Let's first look this:

select * from openjson('["small.jpg","mid.jpg","big.jpg"]')
select * from openjson('{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}')

Result:

key	value	     type
0	small.jpg	1
1	mid.jpg	        1
2	big.jpg	        1

key	value	     type
A	small1.jpg	1
B	mid1.jpg	1
C	big1.jpg	1

Now look at more complehensive example:

create table parts (id int, name varchar(255), images varchar(max))
insert into parts (id,name,images) values 
    (1, 'nail','["small.jpg","mid.jpg","big.jpg"]'),
    (2, 'screw','{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}')

--split array column to rows
declare @tmp table(id int, img varchar(4000))
declare @id int,@img varchar(max)
declare csr cursor for
select id,images from parts
open csr
fetch next from csr into @id,@img
while @@fetch_status=0
begin
    insert into @tmp (id,img) select @id,value from openjson(@img)
    fetch next from csr into @id,@img
end
close csr
deallocate csr
select * from @tmp

Result:

Concat rows to a column

Using OPENXML feature in sql server to concat rows into a column. Reuse to script above:

--concat rows to a column
--using above example
select id, '_product_image_gallery', 
    stuff((select ','+img from @tmp where id=t.id for xml path('')),1,1,'')
from @tmp t
group by id

Result:

Author: IT Team