SQL Server Varchar 数据类型的透视

• 下载源代码 - 515 B

引言

在 SQL Server 中，建议的 Pivot 关系运算符通常应用于数据类型为 numeric 的列。 但是，在某些情况下，业务会要求基于可变字符 (varchar) 数据类型的数据的透视视图。 在此技巧中，我们将介绍如何实现基于 varchar 数据类型的数据透视表。

背景

表 1 显示了一个包含保险索赔相关信息的示例数据集。 根据我在保险行业的工作经验，我知道通常有一组人负责接收和捕获与给定索赔相关的客户文件。 因此，从这个示例数据集可能会出现一个典型的业务案例：作为主管，我希望按策略编号细分用户捕获的文档。

表 1：示例数据集

RecKey	策略	PolType	生效日期	DocID	DocName	捕获者 ID	捕获者
1	Pol002	医院保险	2007/10/01	1	文档 A	NULL	NULL
2	Pol002	医院保险	2007/10/01	4	文档 B	NULL	NULL
3	Pol002	医院保险	2007/10/01	5	文档 C	1	Jane Doe
4	Pol002	医院保险	2007/10/01	7	文档 D	2	John Doe
5	Pol002	医院保险	2007/10/01	10	文档 E	1	Jane Doe

Using the Code

成功执行数据透视表脚本的一些要求包括使用数字输入提供聚合函数。 当您在非数字字段上进行数据透视时，诀窍是识别（或导出）一个可以作为数据透视语法聚合部分输入参数的字段。 幸运的是，在我们的测试数据集中，对于每个捕获者，我们都有一个捕获者 ID。 因此，我们可以使用捕获者 ID 进行聚合。

脚本 1 中提供了在非数字字段上进行数据透视使用的完整脚本

脚本 1

SELECT 
  [PolNumber]
  ,[PolType]
  ,[Effective Date]
  ,a1.[User] AS [Doc A]
  ,a2.[User] AS [Doc B]
  ,a3.[User] AS [Doc C]
  ,a4.[User] AS [Doc D]
  ,a.[User] AS [Doc E]  
FROM (
       SELECT 
         [PolNumber]
         ,[PolType]
         ,[Captured By ID]
         ,[DocName]
         ,CONVERT(VARCHAR,[Effective Date],106) AS [Effective Date]
       FROM [selectSIFISOBlogs].[dbo].[dtTransposeSubs]
     ) AS SourceTable 
PIVOT (AVG([Captured By ID]) 
FOR [DocName] IN ([Doc A],[Doc B],[Doc C],[Doc D],[Doc E])) AS PivotTable
LEFT JOIN [selectSIFISOBlogs].[DIM].[User] a  
  ON a.[UserID]  = PivotTable.[Doc E]
LEFT JOIN [selectSIFISOBlogs].[DIM].[User] a1 
  ON a1.[UserID] = PivotTable.[Doc A]
LEFT JOIN [selectSIFISOBlogs].[DIM].[User] a2  
  ON a2.[UserID] = PivotTable.[Doc B]
LEFT JOIN [selectSIFISOBlogs].[DIM].[User] a3 
  ON a3.[UserID] = PivotTable.[Doc C]
LEFT JOIN [selectSIFISOBlogs].[DIM].[User] a4 
  ON a4.[UserID] = PivotTable.[Doc D]

执行脚本 1 的结果显示在表 2 中。

表 2

Pol002	医院保险	2007 年 10 月 1 日	0	0	Jane Doe	John Doe	Jane Doe
策略	PolType	生效日期	文档 A	文档 B	文档 C	文档 D	文档 E

参考

有关在非数字数据类型上进行数据透视的更多信息，请访问 SQLShack.com。