Split One Column to Multiple Columns


  

--SQL Server 2016, 2017
 
create table source (CODE varchar(100))
Insert into Source values('6-1-A-B')

--Insert into  yourtable (CODE, CODE1,CODE2,CODE3,CODE4)
Select  s.CODE
, Max(Case when rn=1 then value end) CODE1
, Max(Case when rn=2 then value end) CODE2
, Max(Case when rn=3 then value end) CODE3
, Max(Case when rn=4 then value end) CODE4
from Source s
Cross apply (
SELECT ss.[value], ROW_NUMBER() OVER (PARTITION BY s.CODE ORDER BY s.CODE ) AS RN
FROM string_Split(CODE,'-') AS ss
) as d

Group by s.CODE

Drop table source

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/14dbc085-d942-48ba-878a-b83ea590a085/sql-string-split?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s