SQL Server pivoting on non-numeric data types (2023)


In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.

Similarly to the aforementioned article, a denormalized relational representation of our case study looks as shown in Table 1:

RecKeyPolicyPolTypeEffective DateDocIDDocNameSubmittedOutstandingSubmitted DateCaptured By ID
1 Pol002 Hospital Cover 2007/10/01 1 Doc A NULL 1 NULL NULL
2 Pol002 Hospital Cover 2007/10/01 4 Doc B NULL 1 NULL NULL
3 Pol002 Hospital Cover 2007/10/01 5 Doc C 1 NULL 2016/01/13 1
4 Pol002 Hospital Cover 2007/10/01 7 Doc D 1 NULL 2016/01/14 2
5 Pol002 Hospital Cover 2007/10/01 10 Doc E 1 NULL 2016/01/15 1
Table 1: Denormalized relational representation of our claims business study

Workaround #1: Pivot on Dates

A simple business case that can be used to address this workaround is that business may be trying to determine a breakdown of documents captured by date. Ultimately, the aim of such a business case would to have a view of data that looks as shown in Table 2:

PolicyPolTypeEffective DateDoc ADoc BDoc CDoc DDoc E
Pol002 Hospital Cover 01-Oct-07 0 0 13 Jan 2016 14 Jan 2016 15 Jan 2016
Table 2: Pivot on Dates

The first step to pivoting on date fields is to convert your date field to an integer value. Figure 1 shows how we have achieved this through a derived integer field, [Submitted Date INT], which is based off the [Submitted Date] field.

SQL Server pivoting on non-numeric data types (1)

Figure 1: Converting SQL Server date field to integer value

The complete pivot script is depicted in Figure 2:

SQL Server pivoting on non-numeric data types (2)

Figure 2: Pivot on date script

The execution of the script in Figure 3 result into the data shown in Table 3:

PolicyPolTypeEffective DateDoc ADoc BDoc CDoc DDoc E
Pol002 Hospital Cover 01-Oct-07 0 0 20160113 20160114 20160115
Table 3: Results of date pivoted script

However, often business people prefer to view data related dates in a familiar format (i.e. dd/mm/yyyy, dd-mm-yyyy etc.). So we can further extend our script to format the dates according to business friendly format.

There are two ways to achieve this:

1. Formatting SQL Server Date Using Date Dimension

If you write your scripts within a data warehouse or have access to a data warehouse or have a database environment that has a date dimension, then you can make use of the dimension to format the date. Date dimension is a database object that is setup according to a Ralph Kimball’s Data Warehouse design methodology. The dimension usually contain attributes such as date name, quarter, year, and calendar months. Date dimension allows for the breakdown of report data by period over time.

Figure 3 shows some of the content contained in a date dimension.

SQL Server pivoting on non-numeric data types (3)

Figure 3: Sample date dimension data

One of the common ways to lookup data in the date dimension is to use a numeric date key. In our case, we already have derived date column ([Submitted Date INT]) which is in an integer format. Figure 4, shows our revised pivoted script joined to the date dimension.

As it can be seen there are several instances of the dim date to the fields that we are using as pivots. For instance, the first condition is to join dim date to doc a and so on. As argued in the aforementioned article, this way of doing this only suffer from one thing: it is not dynamic – meaning, if business later introduce Doc F as a document that should be submitted in order to process a claim, than a developer would have to refactor the script to include the new doc column and another join condition to dim date to display a custom date format for the newly added column.

SQL Server pivoting on non-numeric data types (4)

Figure 4: Pivoting on date using date dimension

The results of the script depicted in Figure 4 are shown in Table 4:


Effective Date

Doc ADoc BDoc CDoc DDoc E
Pol002 Hospital Cover 01 Oct 2007 0 0 13 Jan 2016 14 Jan 2016 15 Jan 2016
Table 4: Output of pivoting on date using date dimension

2. Convert Integer to SQL Server Date

An alternative to converting a SQL Server date is not to use a join to date dimension instead you convert the numeric values back to business friendly date using T-SQL Convert function. This is done through a nested convert function in which you first convert the numeric value to variable character (varchar), then convert varchar value to date (which is usually in the format yyyy-mm-dd) and finally convert the format of the date to dd mmm yyyy. The complete syntax looks as shown in Figure 5:

SQL Server pivoting on non-numeric data types (5)

Figure 5: Convert numeric value to date

The complete script is shown in Figure 6.

SQL Server pivoting on non-numeric data types (6)

Figure 6: Pivoting on date using convert function

Workaround #2: Pivot on Varchar Data Types

In my experience working within an insurance industry, I have noticed that there are often business users whose job is solely to receive and capture claim documents submitted by clients. Thus, another fitting business case would be to have a breakdown of claim documents captured by users as shown in Table 5.

PolicyPolTypeEffective DateDoc ADoc BDoc CDoc DDoc E
Pol002 Hospital Cover 01 Oct 2007 0 0 Jane Doe John Doe Jane Doe
Table 5: Breakdown of claim documents captured by users

Similar to pivoting on date data types, the key to pivoting on varchar is identifying a field that can be used as an input parameter in the aggregate function. In our case, every user is assigned a numeric user ID as shown in Figure 7 (this user id can then be used to look-up user full name).

SQL Server pivoting on non-numeric data types (7)

Figure 7: Case study with documents captured by ID information

The revised script that now pivots on captured by ID is shown in Figure 8. It can also be seen that after the pivoting of data, we then join back on user id to the User dimension to retrieve associated user full name.

SQL Server pivoting on non-numeric data types (8)

Figure 8: Pivot against varchar data type script


In this article we’ve had a look at available workarounds to pivoting using non-numeric fields. The key to pivoting using non-numeric fields is that you need to find a way to trick the aggregate function as it strictly takes in numeric values. Thus you ought to find a way to either find a numeric surrogate field (i.e. corresponding user id of user field) or derive such a field (i.e. converting date to integer) to be used as input parameters in the aggregate function.


  • Author
  • Recent Posts

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu

Latest posts by Sifiso Ndlovu (see all)

  • Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020
  • Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019
  • Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019

Related posts:

  1. SQL Server Data Warehouse design best practice for Analysis Services (SSAS)
  2. Spatial SQL data types in SQL Server
  3. Implementing SQL data types
  4. An overview of SQL Server data types
  5. SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
Top Articles
Latest Posts
Article information

Author: Mrs. Angelic Larkin

Last Updated: 15/01/2024

Views: 6253

Rating: 4.7 / 5 (67 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Mrs. Angelic Larkin

Birthday: 1992-06-28

Address: Apt. 413 8275 Mueller Overpass, South Magnolia, IA 99527-6023

Phone: +6824704719725

Job: District Real-Estate Facilitator

Hobby: Letterboxing, Vacation, Poi, Homebrewing, Mountain biking, Slacklining, Cabaret

Introduction: My name is Mrs. Angelic Larkin, I am a cute, charming, funny, determined, inexpensive, joyous, cheerful person who loves writing and wants to share my knowledge and understanding with you.