Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick

Pivoting on SQL Server Varchar Data Types

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
22 Feb 2016CPOL2 min read 21.4K   87   2  
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)


Written By
Technical Lead select SIFISO
South Africa South Africa
Sifiso W. Ndlovu is a certified Microsoft professional who holds a Master’s degree in IT Management from the University of Johannesburg. He specializes on a range of enterprise and consumer technologies using open source and proprietary software. He is the member of the Johannesburg SQL User Group wherein he has made several presentations on User Group Meetings and SQL Saturday sessions. He has written for a number of publications including SQLShack.com and SQLServerCentral.com.

Comments and Discussions

 
-- There are no messages in this forum --