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

Transact-SQL
1

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
2

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)
1 Like