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
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
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)
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
19 People are following this question.
