Like This Blog 0

Added by Jeff Jones 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')