自定义的交叉表报告
一种将数据组织到表格中的方法，可帮助您使用 SQL Server 生成大型“交叉表”报告。
引言
本文档提供了一种新的数据组织方法，可帮助您使用 SQL Server 2000/2005 生成大型“交叉表”报告。这是一个自定义解决方案，不使用 SQL Server 2005 中提供的 PIVOT/UNPIVOT 选项。
背景
最近，我遇到了一个新的问题，需要生成一个需要从当前月份追溯 48 个月数据的交叉表报告，该报告需要计算并给出每个月的“年初至今”、“预付款”、“已付金额”、“已收回金额”、“调整金额”和“余额”等各种金额。这些信息需要为每个“服务提供商”提供。总之，我需要创建一个包含跨越 250 列数据的交叉表报告。请不要查看数字并汇总总计。它们将不匹配。提供的数据表用于支持概念和方法。下面是三个月期间的示例报告格式
| 服务提供商总计 | 年初至今（美元） | 预付款金额。 | 已付款金额。 | 已收款金额。 | 调整金额。 | 余额金额。 | 预付款金额。 | 已付款金额。 | 已收款金额。 | 调整金额。 | 余额金额。 |
| 期间 | 03 年 1 月 1 日 | 03 年 1 月 1 日 | 03 年 1 月 1 日 | 03 年 1 月 1 日 | 03 年 1 月 1 日 | 07 年 12 月 1 日 | 07 年 12 月 1 日 | 07 年 12 月 1 日 | 07 年 12 月 1 日 | 07 年 12 月 1 日 | |
|
P1001 |
1312 | 800 | 150 | 12 | 300 | 362 | 1300 | 500 | 100 | 300 | 600 |
|
P1002 |
4080 | 800 | 200 | 0 | 20 | 580 | 4000 | 700 | 350 | 0 | 3650 |
| P1003 | 8605 | 800 | 300 | 300 | 20 | 780 | 9000 | 1900 | 200 | 0 | 7300 |
|
P1004 |
3325 | 800 | 100 | 0 | 75 | 625 | 3000 | 2500 | 1000 | 0 | 1500 |
|
P1005 |
8105 | 800 | 125 | 0 | 20 | 655 | 7000 | 100 | 0 | 900 | 6000 |
这个基本需求使得报告生成过程有点复杂，因为我们仍在运行 SQL 2000，并且 PIVOT/UNPIVOT 选项无法直接使用。使此报告复杂的其他因素是
- XML 数据类型和 XML 数据操作不可用
- 列类型对象数据类型不可用，如 Oracle 8 及以上版本中所提供
解决方案
即使不是 XML 数据类型，对象数据类型也可能违反第一范式 (1NF)，即关系在每一行和每一列中只能包含原子值。对象数据类型列与主键的关系是一对多关系。我们还可以将其视为嵌套表场景，其中主键映射到本身就是表的内存对象。
牢记基本原则，我寻找了多种方法来实现解决方案，而无需编写数千行 T-SQL 代码。最终，我找到了以下解决方案。
在开始查看我创建的实际 SQL 表、存储过程和函数来生成此报告之前，让我们通过一个示例来了解这种方法。
帮助解决此问题的最重要概念是“指针”。那么，什么是指针？指针是数据所在的内存位置的地址。从“C/C++ 语言”的角度来看，这些数据是“结构”、“类”或任何其他基本数据类型的实例。
RDBMS 的其他基本概念，如“唯一索引”和“主键”，也帮助解决了这个问题。
通过将这两种编程构造和数据库概念结合起来，我实现了这种获取“交叉表”报告的方法。
报告的源数据
下面是对需要呈现为上述交叉表报告的结构化数据的摘要。实际上，不同的金额/值来自三个不同的源/表，而不是一个表。为简单起见，我将所有不同的金额/$列以及它们的提供商和处理周期（2007/12 至 2007/07）列在一个表中。此表是您将在示例中看到的 tblClaims 表的单一表表示。
| 序号 | 服务提供商 | 处理周期 | 高级 | 已付 | 已收 | 调整 |
| 1 | P1001 | 200712 | $48.39 | $138.61 | $187.00 | $30.00 |
| 2 | P1002 | 200712 | $142.97 | $107.03 | $250.00 | $30.00 |
| 3 | P1003 | 200712 | $40.46 | $76.54 | $117.00 | $128.00 |
| 4 | P1004 | 200712 | $11.83 | $100.17 | $112.00 | $9.05 |
| 5 | P1005 | 200712 | $9.88 | $145.12 | $155.00 | $9.05 |
| 6 | P1001 | 200711 | $3.00 | $23.00 | $26.00 | $89.35 |
| 7 | P1002 | 200711 | $1,210.05 | $689.95 | $1,900.00 | $8.15 |
| 8 | P1003 | 200711 | $9.05 | $20.95 | $30.00 | $113.60 |
| 9 | P1004 | 200711 | $9.05 | $20.95 | $30.00 | $0.13 |
| 10 | P1005 | 200711 | $9.05 | $20.95 | $30.00 | $51.18 |
| 11 | P1001 | 200710 | $89.35 | $38.65 | $128.00 | $39.84 |
| 12 | P1002 | 200710 | $8.15 | $20.45 | $28.60 | $19.27 |
| 13 | P1003 | 200710 | $18.88 | $39.12 | $58.00 | $519.00 |
| 14 | P1004 | 200710 | $28.73 | $19.27 | $48.00 | $69.00 |
| 15 | P1005 | 200710 | $9.44 | $19.56 | $29.00 | $163.00 |
| 16 | P1001 | 200709 | $28.73 | $19.27 | $48.00 | $36.40 |
| 17 | P1002 | 200709 | $126.18 | $223.82 | $350.00 | $113.60 |
| 18 | P1003 | 200709 | $82.74 | $287.26 | $370.00 | $0.13 |
| 19 | P1004 | 200709 | $192.06 | $265.44 | $457.50 | $51.18 |
| 20 | P1005 | 200709 | $960.32 | $869.68 | $1,830.00 | $39.84 |
| 21 | P1001 | 200708 | $19.09 | $135.91 | $155.00 | $126.18 |
| 22 | P1002 | 200708 | $3.00 | $23.00 | $26.00 | $82.74 |
| 23 | P1003 | 200708 | $57.67 | $461.33 | $519.00 | $192.06 |
| 24 | P1004 | 200708 | $48.39 | $20.61 | $69.00 | $20.61 |
| 25 | P1005 | 200708 | $49.40 | $113.60 | $163.00 | $113.60 |
| 26 | P1001 | 200707 | $36.27 | $0.13 | $36.40 | $0.13 |
| 27 | P1002 | 200707 | $22.82 | $51.18 | $74.00 | $56.66 |
| 28 | P1003 | 200707 | $35.16 | $39.84 | $75.00 | $22.82 |
| 29 | P1004 | 200707 | $33.91 | $26.09 | $60.00 | $5.49 |
| 30 | P1005 | 200707 | $98.17 | $73.83 | $172.00 | $5.49 |
此方法的实现细节
如问题定义中所述，此报告需要在 48 个月期间内显示 6 个不同的金额/美元值。构建一个拥有如此多列（250+）的表并不难，而且大多数 RDBMS（如 SQL Server）都支持这一点，并将它们返回到报告中。但基本思想是将“处理周期行”的数据 PIVOT/转换为“处理周期列”的数据。我实现此解决方案的方式是使用“概念和语言结构”，正如我在本文的“解决方案”部分中所提到的。
作为解决方案的一部分，我创建了两个表。第一个表称为 CrosstabXRef。此表的结构如下所示
表名：CrosstabXRef
| 序号 | 列名 | 数据类型 | 主键 | 是否为空 |
| 1 | SvcProvider | char (10) | 是（唯一索引） | 否 |
| 2 | RecGeneratedDate | datetime | 否 | 否 |
| 3 | Period48 | uniqueidentifier | 否 | 是 |
| 4 | Period47 | uniqueidentifier | 否 | 是 |
| 5 | Period46 | uniqueidentifier | 否 | 是 |
| 6 | … | uniqueidentifier | 否 | 是 |
| 7 | … | uniqueidentifier | 否 | 是 |
| 8 | … | uniqueidentifier | 否 | 是 |
| 9 | Period02 | uniqueidentifier | 否 | 是 |
| 10 | Period01 | uniqueidentifier | 否 | 是 |
请注意这些列（Period48、Period47、Period46、...、Period02、Period01）的数据类型。所有这些列的数据类型均为 uniqueidentifier，它们将保存一个唯一值，该值映射到下面 CrosstabLookup 表中的记录。
表名：CrosstabLookup
| 序号 | 列名 | 数据类型 | 主键 | 是否为空 |
| 1 | PeriodIdentifier | uniqueidentifier | 是 | 否 |
| 2 | 高级 | 数值 | 否 | 是 |
| 3 | 已付 | 数值 | 否 | 是 |
| 4 | 已收 | 数值 | 否 | 是 |
| 5 | 调整 | 数值 | 否 | 是 |
| 6 | 余额 | 数值 | 否 | 是 |
那么，这两张表是如何关联的？答案在于这两张表中 Period## 列的数据类型。如果您猜对了，是的，作为 =NEWID () 方法返回的 GUID 值存储在这些 uniqueidentifier 数据类型列的数据值中。
当给定“期间”和“服务提供商”的“索赔”付款和调整选择中存在一条记录时，将在 CrosstabLookup 表中创建一个条目。此过程将持续选定的“48”个月窗口。如果某个“Svc Provider”在特定“Period”没有活动，则不会向此查找表插入记录。但这种情况不太可能发生，因为每月都会向“Svc Provider”支付预付款。
因此，总而言之，数据库中的数据将如下图所示
为了实现这一点，我需要创建两个数据库对象：一个存储过程来构造上述两个表，以及一个函数来根据 uniqueidentifier 和索引返回 CrosstabLookup 中存储的数据。
SQL 代码块是我创建的存储过程，它将数据加载到 CrosstabLookup 和 CrosstabXRef 中。此存储过程的名称为 spProcessClaimsCrosstabMaping。
CREATE PROCEDURE dbo.spProcessClaimsCrosstabMaping
AS
SET CONCAT_NULL_YIELDS_NULL OFF
Declare @SvcProv varchar(10)
Declare @Advance numeric
Declare @PaidAmt numeric
Declare @RecAmt numeric
Declare @AdjAmt numeric
Declare @GUID uniqueidentifier
Declare @DynamicSQL varchar(3000)
Declare @ProcessPeriod numeric
Declare @YearPart Varchar(4)
Declare @MonthPart Varchar(2)
Declare @CharYear Varchar(6)
Declare @StartDate datetime
Declare @EndDate datetime
DECLARE SvcProvCursor CURSOR READ_ONLY
FOR
Select distinct ServiceProvider from tblClaims
OPEN SvcProvCursor
FETCH NEXT FROM SvcProvCursor INTO @SvcProv
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @ProcessPeriod = 200706
SET @EndDate = GETDATE()
-- THE STATIC NUMBER SHOULD BE 1 MONTH LESS TO THE ACTUAL MONTHS REQUIRED
SET @StartDate = DATEADD(mm,-12, @EndDate)
SET @DynamicSQL = ' INSERT INTO dbo.CrosstabXRef ' + ' values (''' + _
@SvcProv + ''',''' + CAST(@StartDate AS Varchar(20)) + ''','
WHILE (@StartDate <= @EndDate)
BEGIN
SET @YearPart = CAST(DATEPART(yyyy, @StartDate) AS VARCHAR(4))
SET @MonthPart = CAST(DATEPART(mm, @StartDate) AS VARCHAR(2))
IF LEN(@MonthPart) = 1
SET @MonthPart = '0' + @MonthPart
SET @CharYear = @YearPart + @MonthPart
SET @ProcessPeriod = CAST(@CharYear AS numeric)
--PRINT @ProcessPeriod
SET @GUID = NULL
Select @Advance = Advance ,
@PaidAmt = Paid , @RecAmt = Received,
@AdjAmt = Adjustment
from tblClaims tc where tc.ProcessDate = @ProcessPeriod and
ServiceProvider=@SvcProv
if @@ROWCOUNT = 1
BEGIN
SET @GUID = NEWID()
--PRINT @GUID
INSERT INTO dbo.CrosstabLookup
VALUES
-- ("PeriodIdentifier", "Advance", "Paid", "Received", "Adjustment")
(@GUID, @Advance, @PaidAmt, @RecAmt, @AdjAmt)
SET @DynamicSQL = @DynamicSQL + '''' + _
CAST(@GUID AS varchar(64)) + ''''
END
ELSE --END OF IF BLOCK
BEGIN
-- NO GUID VALUE WILL BE SET TO MAP THIS COLUMN WITH A RECORD
-- IN THE MAPPING TABLE
SET @DynamicSQL = @DynamicSQL + 'NULL'
END --END OF ELSE BLOCK
if (@StartDate <> @EndDate)
BEGIN
IF (@GUID <> NULL)
SET @DynamicSQL = @DynamicSQL + ''','
ELSE
SET @DynamicSQL = @DynamicSQL + ','
END
SET @StartDate = DATEADD(mm,1, @StartDate)
END -- END OF THE WHILE LOOP
IF (@GUID <> NULL)
SET @DynamicSQL = @DynamicSQL + ''')'
ELSE
SET @DynamicSQL = @DynamicSQL + ')'
--PRINT @DynamicSQL
PRINT 'WORKING....!!'
EXEC (@DynamicSQL)
FETCH NEXT FROM SvcProvCursor INTO @SvcProv
END
CLOSE SvcProvCursor
PRINT 'FINISHED PROCESSING...!!'
GO
我创建的第二个函数用于呈现数据，名为 fnClaimsCrosstabHpr。这将帮助我们根据 PeriodIdentifier （它间接映射到服务提供商）获取美元值。
CREATE FUNCTION dbo.fnClaimsCrosstabHpr(@PeriodId uniqueidentifier, @ColumnIndes int)
RETURNS float
AS
BEGIN
Declare @Amount float
if (@ColumnIndes = 1) Select @Amount=_
Advance from CrosstabLookup where PeriodIdentifier = @PeriodId
if (@ColumnIndes = 2) Select @Amount=_
Paid from CrosstabLookup where PeriodIdentifier = @PeriodId
if (@ColumnIndes = 3) Select @Amount=_
Received from CrosstabLookup where PeriodIdentifier = @PeriodId
if (@ColumnIndes = 4) Select @Amount=_
Adjustment from CrosstabLookup where PeriodIdentifier = @PeriodId
RETURN(@Amount)
END
下一步是执行存储过程以填充 CrosstabLookup 和 CrosstabXRef 表中的数据。完成此操作后，下一步是生成报告本身。下面是一个帮助生成 Crosstab 报告数据表的示例查询。这些数据将直接导出到 Excel 进行进一步分析。
select SvcProvider, RecGeneratedDate,
dbo.fnClaimsCrosstabHpr(Period48, 1) AS Advance_48,
dbo.fnClaimsCrosstabHpr(Period48, 2) AS Paid_48,
dbo.fnClaimsCrosstabHpr(Period48, 3) AS Received_48,
dbo.fnClaimsCrosstabHpr(Period48, 4) AS Adjustment_48,
dbo.fnClaimsCrosstabHpr(Period47, 1) AS Advance_47,
dbo.fnClaimsCrosstabHpr(Period47, 2) AS Paid_47,
dbo.fnClaimsCrosstabHpr(Period47, 3) AS Received_47,
dbo.fnClaimsCrosstabHpr(Period47, 4) AS Adjustment_47,
dbo.fnClaimsCrosstabHpr(Period01, 1) AS Advance_01,
dbo.fnClaimsCrosstabHpr(Period01, 2) AS Paid_01,
dbo.fnClaimsCrosstabHpr(Period01, 3) AS Received_01,
dbo.fnClaimsCrosstabHpr(Period01, 4) AS Adjustment_01
from CrosstabXRef
附件
附有本文中列出的 SQL 脚本和 SQL 对象副本供下载。
结论
为了撰写本文，我对实际表和 SQL 数据库对象进行了一些更改。我希望我所做的更改没有造成差异，导致本文档难以阅读。如果您有任何问题，请写信给我。我将尽力解决。
享受阅读学习的乐趣。