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.