An example to PIVOT on a column with undermined unique values

  • Article

SQL Server T-SQL PIVOT is a great feature that you can turn the unique values from one column into multiple into multiple columns in the output table result. It also works great if you can iterate all unique values in the column to be pivoted , but the problem is that sometimes you don't know that information, or you have multiple unique values which is difficult to list all of them in the query.

With help from my friend (many thanks to Stephanie), it is easy to address this problem using FOR XML PATH. Here is an example based on AdvenutreWorks2012 sample database:

  use [AdventureWorks2012];
 go
 
 set nocount on;
 
 -- For each product sub category, get avergae cose for differnt DaysToManufacture
 DECLARE @cols AS NVARCHAR(MAX),
 @query AS NVARCHAR(MAX);
 
 SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.DaysToManufacture) 
 FROM Production.Product as p
 FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)') 
 ,1,1,'')
 
 set @query = 'SELECT ProductSubcategoryID, ' + @cols + ' from 
 (
 select p.ProductSubcategoryID
 , p.DaysToManufacture
 , p.StandardCost
 from Production.Product as p
 where p.ProductSubcategoryID IS NOT NULL
 ) x
 pivot 
 (
 avg(StandardCost)
 for DaysToManufacture in (' + @cols + ')
 ) p '
 
 execute(@query);

Hope it helps!