I have few hundred thousand records to pivot dynamically. I've tried the following query and it works fine for a selected emploee id.
and when i want to pivot all the records of the employees, it is executing a very long time. Is it the right way to do or any better way to do? I have stopped the execution after half-an-hour as i didn't get an result.
What I have tried:
<pre lang="SQL">declare @cols nvarchar(max)=N'',@sql nvarchar(max)=N'',@uniqcols nvarchar(max)=N''
select @cols=isnull(@cols+',','')+QUOTENAME(concat(format(FromDate,'Y'),' to ',format(ToDate,'Y')))
from salaries
where EmpId=15001
print @cols
set @sql= 'select Firstname '+@cols+'
from
(
select e.Empid,e.Firstname,concat(format(s.FromDate,''Y''),'' to '',format(s.ToDate,''Y'')) as period,s.salary
from employees e join salaries s
on e.EmpId=s.Empid
where e.EmpId=15001
)as source
pivot
(
sum(salary) for period in ('+stuff(@cols,1,1,'')+')
)as derived'
print @sql
exec sp_executesql @sql
<pre lang="SQL">declare @cols nvarchar(max)=N'',@sql nvarchar(max)=N'',@uniqcols nvarchar(max)=N''
select @cols=isnull(@cols+',','')+QUOTENAME(concat(format(FromDate,'Y'),' to ',format(ToDate,'Y')))
from salaries
print @cols
set @sql= 'select Firstname '+@cols+'
from
(
select e.Empid,e.Firstname,concat(format(s.FromDate,''Y''),'' to '',format(s.ToDate,''Y'')) as period,s.salary
from employees e join salaries s
on e.EmpId=s.Empid
)as source
pivot
(
sum(salary) for period in ('+stuff(@cols,1,1,'')+')
)as derived'
print @sql
exec sp_executesql @sql
Is there any other way to boost this dynamic pivot query within less time.
Thanks in advance.