SQLTeam.com | Weblogs | Forums

How to create dynamic sql substring based on field name function id without writing static?

How to create dynamic SQL sub string based on field name functionid without writing static ?

I have table name DoneCode i need when add new function on table Done code

automatically without rewrite or modify my code

so that i need to do

substring (DoneCode,@FunctionId,1)

but i done know how to do that

so suppose tomorrow add new Function as

10,PCN

so no need to add new function in code as substring(DoneCode,10,1) as PCN

I need to use substring(DoneCode,FunctionId,1) as FunctionId

but i dont know how to make within loop

so can you help me

create table #Donecode
(
FunctionId int,
FunctionName nvarchar(50)
)
insert into #Donecode
values
(1,'Lifecycle'),
(2,'Rohs'),
(3,'Reach'),
(4,'FMD'),
(5,'Parametric'),
(6,'Package'),
(7,'IntroductionDate'),
(8,'MFG'),
(9,'Qualification')


create table #filedetails
(
FileID  int,
DoneCode nvarchar(50)
)
insert into #filedetails (FileID,DoneCode) 
values 
(3301,'101011111110'),
(3301,'101101111111'),
(3301,'101001000011')
select  substring (Donecode,1,1)as Lifecycle,substring (Donecode,2,1)as Rohs,substring (Donecode,3,1)as Reach,substring (Donecode,4,1)as FMD,substring (Donecode,5,1)as Parametric,substring (Donecode,6,1)as Package,substring (Donecode,7,1)as IntroductionDate,substring (Donecode,8,1)as MFG,substring (Donecode,9,1)as Qualification
into #FunctionsDiv  from #filedetails where DoneCode is not NULL and fileid=3301

drop table #filedetails
drop table #Donecode
drop table #FunctionsDiv

You can use dynamic pivot

DECLARE @Colslist VARCHAR(MAX),
		@ColsAlias varchar(max) ,
		@SQL varchar(max)
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT  FunctionName  
FROM #DoneCode
order by FunctionID
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  ,
	   @ColsAlias =  COALESCE(@ColsAlias + ',max([', 'max([') + Head + ']) as ' + Head  
FROM @Cols t  
 
set @SQL = 'select DoneCode, ' + @ColsAlias  + ' from (
                SELECT Substring (donecode,functionid,1) substr, 
                       functionname, 
                       functionid, 
                       donecode 
                  FROM #filedetails 
                    CROSS apply #donecode 
                 WHERE       donecode IS NOT NULL 
                   AND         fileid=3301) v
pivot (max(substr) for FunctionName in (' + @Colslist + ')) PVT 
GROUP BY donecode'

exec (@SQL)