Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2012

Pivoting on SQL Server Varchar Data Types

5.00/5 (2 votes)
22 Feb 2016CPOL2 min read 22.2K   88  
This tip illustrates a workaround to SQL Server pivoting on variable character data types.

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

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)