question

vsrini008 avatar image
vsrini008 asked

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 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
sql-server-2008sql-server-2005sqlsql-server-2008-r2
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image Dave_Green ♦ commented ·
Hello, You appear to have posted a 'before' and 'after' query set - however the text explaining your issue appears to be lost. Can you post the code you have tried, and the nature of the problem you have hit (as opposed to a description of what you are trying to achieve) so we can better help you? Thanks
0 Likes 0 ·
vsrini008 avatar image vsrini008 commented ·
I have tried for 1 column i.e. (Roll Code) can we make it for other columns 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)
0 Likes 0 ·

1 Answer

·
Gazz avatar image
Gazz answered
Use CHARINDEX to find the location of the ; then take the left and the right (You will have to add something else in for when there is no ;) [ https://msdn.microsoft.com/en-us/library/ms186323.aspx][1] with this you can make: LEFT(RACK,CHARINDEX(';',RACK)) and RIGHT(RACK,LEN(RACK)-CHARINDEX(';',RACK)) [1]: https://msdn.microsoft.com/en-us/library/ms186323.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.