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.
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:
,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]
,[Captured By ID]
,CONVERT(VARCHAR,[Effective Date],106) AS [Effective Date]
) 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.
|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 |
For more on the rest of pivoting on non-numeric data types, go to SQLShack.com.