7
Reply

Pivot in SQL Server 2014

Hold On

Hold On

Jun 6 2017 3:23 AM
219
Hello, I need to do a pivot in sql server 2014 but failed. I read several forums but none of them helped. Below is my Table that I would like to pivot:
 
 
 Below is my desired result:
 
 
 This is what I have so far but have an error:
  1. DECLARE   @Query AS NVARCHAR(MAX)  
  2. DECLARE   @PivotProductType AS NVARCHAR(MAX)  
  3.    
  4. --Get unique values of pivot column    
  5. SELECT   @PivotProductType= COALESCE(@PivotProductType + ',','') + QUOTENAME(ProductType)  
  6. FROM (SELECT DISTINCT ProductType FROM tblAgentCommission) AS PivotColumn  
  7.    
  8. SELECT   @PivotProductType  
  9.    
  10. --Create the dynamic query with all the values for   
  11. --pivot column at runtime  
  12. SET   @Query =   
  13.     N'SELECT Country, ' +   @PivotProductType + '  
  14.     FROM [dbo].[PivotColumn]   
  15.     PIVOT(CommissionRate)   
  16.           FOR ProductType IN (' + @PivotProductType + ')) AS P'  
  17.    
  18. SELECT   @Query  
  19. --Execute dynamic query  
  20. EXEC sp_executesql @Query  
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
 
Thanks in Advance. 

Answers (7)