如何为SQL Server中的数百条记录生成动态数据透视表 [英] How do I generate a dynamic pivot for few hundreds of records in SQL server

问题描述

我有几十万条记录动态转动。我已经尝试了以下查询，它适用于选定的员工ID。

当我想转动员工的所有记录时，它正在执行很长时间。这是正确的做法还是更好的方式？我已经停止了半小时后的执行，因为我没有得到结果。



我尝试了什么：

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.

推荐答案

检查我的过去的答案： 数据透视表中的每月总和 [ ^ ]

Check my past answer: Monthly sum in pivot table[^]

这篇关于如何为SQL Server中的数百条记录生成动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！