1
Answer

Dynamic Pivot in SQL Server

Tarun Kumar

Tarun Kumar
7y
532
1
I have few hundred thousand records to pivot dynamically. I've tried the following query and it works fine for a selected emploeeid.
  1. declare @cols nvarchar(max)=N'',@sql nvarchar(max)=N'',@uniqcols nvarchar(max)=N''  
  2. select @cols=isnull(@cols+',','')+QUOTENAME(concat(format(FromDate,'Y'),' to ',format(ToDate,'Y')))  
  3. from salaries  
  4. where EmpId=15001  
  5. print @cols  
  6. set @sql= 'select Firstname '+@cols+'  
  7. from  
  8. (  
  9. select e.Empid,e.Firstname,concat(format(s.FromDate,''Y''),'' to '',format(s.ToDate,''Y'')) as period,s.salary  
  10. from employees e join salaries s  
  11. on e.EmpId=s.Empid  
  12. where e.EmpId=15001  
  13. )as source  
  14. pivot  
  15. (  
  16. sum(salary) for period in ('+stuff(@cols,1,1,'')+')  
  17. )as derived'  
  18. print @sql  
  19. exec sp_executesql @sql  
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.
  1. declare @cols nvarchar(max)=N'',@sql nvarchar(max)=N'',@uniqcols nvarchar(max)=N''  
  2. select @cols=isnull(@cols+',','')+QUOTENAME(concat(format(FromDate,'Y'),' to ',format(ToDate,'Y')))  
  3. from salaries  
  4. print @cols  
  5. set @sql= 'select Firstname '+@cols+'  
  6. from  
  7. (  
  8. select e.Empid,e.Firstname,concat(format(s.FromDate,''Y''),'' to '',format(s.ToDate,''Y'')) as period,s.salary  
  9. from employees e join salaries s  
  10. on e.EmpId=s.Empid  
  11. )as source  
  12. pivot  
  13. (  
  14. sum(salary) for period in ('+stuff(@cols,1,1,'')+')  
  15. )as derived'  
  16. print @sql  
  17. exec sp_executesql @sql  
thanks in advance
Answers (1)
Next Recommended Forum