Pivoting on SQL Server Varchar Data Types

• Download source - 515 B

Introduction

The recommended usage of the Pivot relational operator in SQL Server is usually applied against columns whose data type is numeric. However, there are instances whereby business requests for a pivoted view of data that is based off variable character (varchar) data type. In this tip, we take a look at how pivoting on varchar data type can be achieved.

Background

Table 1 displays a sample dataset that contains information related to an insurance claim. In my experience working in the insurance industries, I know that there is usually a group of people whose job is to receive and capture customer's documents related to a given claim. Thus, a typical business case that can arise from this sample dataset could be: As a supervisor, I would like a breakdown of users by captured documents per policy number.

Table 1: Sample Dataset

RecKey	Policy	PolType	Effective Date	DocID	DocName	Captured By ID	Captured By
1	Pol002	Hospital Cover	2007/10/01	1	Doc A	NULL	NULL
2	Pol002	Hospital Cover	2007/10/01	4	Doc B	NULL	NULL
3	Pol002	Hospital Cover	2007/10/01	5	Doc C	1	Jane Doe
4	Pol002	Hospital Cover	2007/10/01	7	Doc D	2	John Doe
5	Pol002	Hospital Cover	2007/10/01	10	Doc E	1	Jane Doe

Using the Code

Some of the requirements of a successful Pivot script execution involves supplying the aggregate function with a numeric input. When you are pivoting on non-numeric field, the trick would be to identify (or derive) a field that can be used as an input parameter to the aggregation part of the pivot syntax. Luckily in our test dataset, for every Captured By we have a Captured By ID. Thus, we can aggregate using the Captured By ID.

The complete script used in pivoting on non-numeric field, is provided in Script 1:

Script 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]

The results of executing Script 1 are shown in Table 2.

Table 2

Pol002	Hospital Cover	01 Oct 2007	0	0	Jane Doe	John Doe	Jane Doe
Policy	PolType	Effective Date	Doc A	Doc B	Doc C	Doc D	Doc E

Reference

For more on the rest of pivoting on non-numeric data types, go to SQLShack.com.