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

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

How to clone a Windows Server 2012 or 2012 R2 Domain Controller

3 1481 3

One of the coolest new features in Window Server 2012 and Windows Server 2012 R2 is the ability to clone a Domain Controller. In the past, if we had virtualized Domain Controllers and we actually took a snapshot of it and then rolled back to that snapshot, it would break the logon service on that … Continue reading How to clone a Windows Server 2012 or 2012 R2 Domain Controller

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 608 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

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