动态交叉表和数据透视表，一种不同的方法
4.17/5 (5投票s)
在本文中，我们将看到一个存储过程，用于从现有表创建具有多个汇总函数的数据透视表。
引言
在本文中，我们将看到一个存储过程，用于从现有表创建具有多个汇总函数的数据透视表。
背景
我是一家中型公司的资深软件工程师。有时我的工作需要我从 SQL Server 中提取一些即席报告。在许多此类报告中，我必须像数据透视表一样将行旋转为列。在 SQL Server 2000 之前，除了使用带有 group by 子句的 case when 语句外，没有其他方法可以实现此目标，SQL Server 2005 之后，它在 select 语句中添加了数据透视表功能。但这不太可定制且使用起来很复杂（至少对我来说）。
Using the Code
许多人提出了他们自己的动态交叉表和数据透视表版本。但我找不到任何完美的版本，它可以接受多个汇总函数并返回具有自定义列标题的数据，所以我决定编写一个存储过程来完成此任务。为了使其更通用并可用于任何表，让我们将表名、字段名、数据透视列和汇总函数作为参数传递给存储过程。
现在让我们创建一个测试表并用一些相关数据填充该表。以下脚本将创建一个表并填充 100 行数据，这是一个虚拟销售表。该表包含十个客户及其五种不同产品的销售额和收入的记录。
--Delete dbo.test table if it already exist
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test
GO
--Create test table with four columns
CREATE TABLE dbo.test
(
Customer_Name varchar(100) null,
Product_Name varchar(100) null,
Quantity numeric(18,2) null,
Revenue money null
)
GO
--fill test table with 100 rows
DECLARE @cnt INT; SET @cnt = 1
WHILE @cnt <=100 --will loop times
BEGIN
SET @cnt = @cnt + 1
/*
Insert a single row in test table, we're creating
customer name, product name, quantity and revenue
randomly using RAND() function
*/
insert into test (Customer_Name,Product_Name,Quantity,Revenue)
values ('Customer' + convert(varchar,floor(1+(10)*RAND())),
'Product' + convert(varchar,floor(1+(5)*RAND())),
floor(10+ (100-10)*RAND()), floor(1000 + (10000-1000)*RAND()))
END
--result
select * from test
这里我们有一个包含 100 行的表。现在我们可以在此表上运行报告。我们希望我们的报告如下
- 将不同的客户名称作为行
- 将产品名称与数量和收入作为列
在创建存储过程之前，我们必须分析我们想要什么。我们所需要的只是一个 SQL select 语句，如下所示
Select Customer_Name
, Sum(case when Product_Name='Product1' then Quantity else 0 end) [Product1 Quantity]
, Sum(case when Product_Name='Product2' then Quantity else 0 end) [Product2 Quantity]
, Sum(case when Product_Name='Product3' then Quantity else 0 end) [Product3 Quantity]
, Sum(case when Product_Name='Product4' then Quantity else 0 end) [Product4 Quantity]
, Sum(case when Product_Name='Product5' then Quantity else 0 end) [Product5 Quantity]
, sum(case when Product_Name='Product1' then Revenue else 0 end) [Product1 Revenue]
, sum(case when Product_Name='Product2' then Revenue else 0 end) [Product2 Revenue]
, sum(case when Product_Name='Product3' then Revenue else 0 end) [Product3 Revenue]
, sum(case when Product_Name='Product4' then Revenue else 0 end) [Product4 Revenue]
, sum(case when Product_Name='Product5' then Revenue else 0 end) [Product5 Revenue]
from test
group by Customer_Name
但我们需要动态创建它，因为我们的产品名称和汇总字段不是恒定的。要创建该 string，首先我们需要循环遍历汇总函数，然后在该循环中，我们必须循环遍历不同的产品名称。我们将获得以逗号分隔的汇总函数作为存储过程的参数之一，我们可以使用以下查询检索唯一的产品名称
我们可以使用游标循环遍历这些记录，或者将其转换为逗号分隔的字符串。 让我们选择第二种方案。 为此，我使用了 COALESCE 函数。
存储过程的完整脚本如下
--Drop Stored Procedure if it already exists
IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'sp_PivotTable')
DROP PROCEDURE dbo.sp_PivotTable
GO
--Create Stored Procedure
CREATE PROCEDURE dbo.sp_PivotTable
@strTable varchar(200)=null, --Parameters Table Name (Required)
@strFields varchar(1000)=null, --Comma Separated Field List (Required)
@strPivotColumn varchar(200)=null, --Pivot Column (Required)
@strSummaryFn varchar(200)=null, --Comma Separated Summary Functions (Required)
@strWhereCnd varchar(1000)=null --Where condition (Optional)
AS
begin
set nocount on
begin try
if @strTable is not null and @strFields is not null and _
@strPivotColumn is not null and @strSummaryFn is not null
begin
--print 'Parameters OK'
declare @strWhereCondition varchar(1000);
if @strWhereCnd is not null
set @strWhereCondition=' where ' + @strWhereCnd;
else
set @strWhereCondition=' ';
declare @strFinalSQL varchar(max); --for storing final query
set @strFinalSQL='Select ' + @strFields + ' ';
--Splitting Comma Separated Summary Functions
set @strSummaryFn=@strSummaryFn + ','
declare @strStart varchar(200)
declare @strFunction varchar(200)
declare @strFnFld varchar(200)
--first loop start
while charindex(',',@strSummaryFn)>0
begin
set @strStart=substring(@strSummaryFn,1,charindex(',',@strSummaryFn)-1);
set @strSummaryFn=substring(@strSummaryFn,charindex_
(',',@strSummaryFn)+1,len(@strSummaryFn) - charindex(', ',@strSummaryFn));
set @strFunction=substring(@strStart,1,charindex('_
(',@strStart)-1); --we'll get function name eg: sum
set @strFnFld=substring(@strStart,charindex('(',@strStart)+1, _
len(@strStart)-(charindex('_
(',@strStart)+1)); --we'll get function field eg: quantity
--Creating dynamic columns
declare @strResult nvarchar(max), @strXML varchar(max);
DECLARE @ParmDefinition nvarchar(max);
--I used COALESCE() function, u can use cursor or any thing
--that you find suitable
set @strResult = 'select @StrOut=COALESCE(@StrOut,'''') + _
convert(varchar(max),pc) + '',''
from (select distinct ' + @strPivotColumn + ' [pc] from '
+ @strTable + @strWhereCondition + ') v order by pc';
SET @ParmDefinition = '@StrOut varchar(max) OUTPUT';
EXECUTE sp_executesql @strResult, @ParmDefinition, @StrOut=@strXML OUTPUT;
declare @strStart2 varchar(max);
--second loop start
while charindex(',',@strXML)>0
begin
set @strStart2=substring(@strXML,1,charindex(',',@strXML)-1); --column name
set @strXML=substring(@strXML,charindex(',',@strXML)+1, _
len(@strXML));--reset the string
set @strFinalSQL=@strFinalSQL + ', ' + @strFunction + _
'( case when ' + @strPivotColumn +'=''' + @strStart2 + _
''' then ' + @strFnFld + ' else 0 end) [' + @strStart2 + ' ' _
+ @strFnFld +'] '; --creates the final string
end
--second loop end
end
--first loop end
set @strFinalSQL=@strFinalSQL + ' from ' + @strTable + _
@strWhereCondition + ' group by ' + @strFields;
print @strFinalSQL; --to display the final string
exec(@strFinalSQL); --executes the final query
end
else
begin
print 'Parameters not provided';
end
end try
begin catch
select isnull(ERROR_MESSAGE(),'') + ' Please contact kannankr.in' AS ErrorMessage;
end catch
end
go
EXECUTE dbo.sp_PivotTable @strTable='test', @strFields='Customer_Name',
@strPivotColumn='Product_Name', @strSummaryFn='Sum(Quantity), sum(Revenue)',
@strWhereCnd='1=1';
go
这里我们得到了结果。它有客户名称作为行和按产品划分的数量和收入作为列（Product1 Quantity, Product2 Quantity … Product1 Revenue, Product2 Revenue…）。创建此存储过程后，您可以使用它轻松提取任何类型的数据透视表报告。
如果您觉得它有用或有更好的解决方案，请告诉我您的宝贵反馈。谢谢。