Dynamic PIVOT by SQL Server Instructor Jeff Jones

Home > Blogs > SQL Server > Dynamic PIVOT by SQL Server Instructor Jeff Jones

Dynamic PIVOT by SQL Server Instructor Jeff Jones

Like This Blog 0 Jeff Jones
Added by December 14, 2006

One aspect of using the PIVOT is to include a list of columns that will be used as the crosstab of the query.  For example, if you wish to display the total quantity ordered by Productid by Year you need to include the list of years for the query.  This unfortunately must be a static list of values.  So as you add years to the table, the query must be modified to include the new year.  We discussed different ways to build a dynamic query to generate the list of years based on the data and insert that list into the PIVOT clause. 

Following are a couple of scripts to solve the problem.  The first one generates a sum of orderqty by productid pivoted by the duedate year.  The second one generate a sum and avg of orderqty by productid by the duedate year.  This one must UNION two PIVOT queries into a single result set.

— Dynamically build the list of years based on the years in
— the purchasing.purchaseorderdetail table. 
— Then use the list to perform a PIVOT operation displaying the
— ProductID in each row and the sum of orderqty by Year.

DECLARE @Yearlist VARCHAR(MAX)
DECLARE @Years TABLE (Year int)

INSERT @Years (year)
 SELECT DISTINCT top 100 percent YEAR(duedate)
 FROM purchasing.purchaseorderdetail
 ORDER BY YEAR(duedate)

SELECT @YearList = COALESCE(@YearList + ',[', '[') +
CAST(year AS CHAR(4)) + ']'
FROM @Years t

EXEC ('SELECT *
FROM (SELECT productid, YEAR(duedate) theyear, orderqty
FROM purchasing.PurchaseOrderDetail) t
PIVOT (SUM(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT')

— Dynamically build the list of years based on the years in
— the purchasing.purchaseorderdetail table. 
— Then use the list to perform a PIVOT operation displaying the
— ProductID in each row and the sum of orderqty by Year.
— Union this result with the same query performing an AVG on the
— orderqty by productid. Included a literal column to identify
— if the data is a sum or average.

DECLARE @Yearlist VARCHAR(MAX)
DECLARE @Years TABLE (Year int)

INSERT @Years (year)
 SELECT DISTINCT top 100 percent YEAR(duedate)
 FROM purchasing.purchaseorderdetail
 ORDER BY YEAR(duedate)

SELECT @YearList = COALESCE(@YearList + ',[', '[') +
CAST(year AS CHAR(4)) + ']'
FROM @Years t

EXEC ('SELECT *
 FROM (SELECT  productid, ''sum'' type, YEAR(duedate) theyear, orderqty
  FROM purchasing.PurchaseOrderDetail) t
 PIVOT (SUM(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT'
 + 'UNION ' +
 'SELECT *
 FROM (SELECT  productid, ''avg'' type, YEAR(duedate) theyear, orderqty
  FROM purchasing.PurchaseOrderDetail) t
 PIVOT (AVG(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT ' +
 'ORDER BY productid')

There is one more technique for generating the comma-delimited list of years using the FOR XML PATH option of a SELECT statement.  I found this technique in Itzik Ben-Gan's new T-SQL Querying book.  I decided to apply it to our dynamic pivot problem to see if we can come up with a simpler way to generate the list of years for the PIVOT operator. I quess we can debate whether this is simpler, at least it gets rid of the need to create a table variable and eliminates a statement.

This technique takes advantage of the PATH option on the FOR XML clause.  If you specify an empty string in the PATH option it does not place any XML tags around the text.  The STUFF function deletes the first comma generated in the string. 

Following is a rewrite of the simple pivot above using this technique.

DECLARE @Yearlist VARCHAR(MAX)

SELECT
@Yearlist = STUFF((SELECT ', [‘ + DYear + ‘]’ AS [text()]
FROM (SELECT DISTINCT
      CONVERT(VARCHAR, YEAR(duedate)) dyear  
      FROM purchasing.purchaseorderdetail) Y   
      ORDER BY dyear
      FOR XML PATH('')), 1, 1, '')

EXEC ('SELECT *
FROM (SELECT productid, YEAR(duedate) theyear, orderqty
FROM purchasing.PurchaseOrderDetail) t
PIVOT (SUM(orderqty) FOR theyear IN (' + @Yearlist + ')) PVT')

Videos You May Like

Creating Dynamic DNS in Network Environments

0 629 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 713 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Government Edition – Encrypting a USB Flash Drive in Windows 10

0 272 2

In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.