Pivot in SQL Server 2014

Question

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: DECLARE @Query AS NVARCHAR( MAX ) DECLARE @PivotProductType AS NVARCHAR( MAX ) --Get unique values of pivot column SELECT @PivotProductType= COALESCE (@PivotProductType + ',' , '' ) + QUOTENAME(ProductType) FROM ( SELECT DISTINCT ProductType FROM tblAgentCommission) AS PivotColumn SELECT @PivotProductType --Create the dynamic query with all the values for --pivot column at runtime SET @Query = N 'SELECT Country, ' + @PivotProductType + ' FROM [dbo].[PivotColumn] PIVOT(CommissionRate) FOR ProductType IN ( ' + @PivotProductType + ' )) AS P' SELECT @Query --Execute dynamic query 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.

Manish Kumar · Answer

Try this....and adjust your column. use print command to analyse whether query is correct or not. SET @Query = N'select AgentID,HPA,motor as MotorInsurance,PA from (SELECT * FROM [dbo].[tblAgentCommission] ) a PIVOT(sum(CommissionRate) FOR ProductType IN (' + @PivotProductType + ')) AS P' print @Query --SELECT @Query --Execute dynamic query EXEC sp_executesql @Query

Manish Kumar · Answer

IF below input is your requirement and table script like CREATE TABLE [dbo].[tblAgentCommission]( [AgentID] [nvarchar](50) NULL , [ProductType] [nvarchar](50) NULL , [CommissionRate] [ decimal ](2, 2) NULL , [UserName] [nvarchar](50) NULL ) ON [ PRIMARY ] Agent ID ProductionType CommisionRate UserName User1 Motor 0.20 Admin then why you need pivot you can get the result by wrtiting simple select statement

Manish Kumar · Answer

Unfortunatley, am not able to help you on dot net side as am working as DBA.

Jes Sie · Answer

@Manish Kumar, thanks for your help. With your insights I was able to get the correct query as below: DECLARE @Query AS NVARCHAR( MAX ) DECLARE @PivotProductType AS NVARCHAR( MAX ) --Get unique values of pivot column SELECT @PivotProductType= COALESCE (@PivotProductType + ',' , '' ) + QUOTENAME(ProductType) FROM ( SELECT DISTINCT ProductType FROM tblAgentCommission) AS ProductType SELECT @PivotProductType --Create the dynamic query with all the values for --pivot column at runtime SET @Query = N 'SELECT AgentID, ' + @PivotProductType + ' FROM [dbo].[tblAgentCommission] PIVOT( SUM (CommissionRate) FOR ProductType IN ( ' + @PivotProductType + ' )) AS P' SELECT @Query --Execute dynamic query EXEC sp_executesql @Query And gives me with what I wanted as below: I made this query into a stored procedure as below: CREATE PROCEDURE spViewAgentsWithCommissionPerProduct -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; -- Insert statements for procedure here DECLARE @Query AS NVARCHAR( MAX ) DECLARE @PivotProductType AS NVARCHAR( MAX ) --Get unique values of pivot column SELECT @PivotProductType= COALESCE (@PivotProductType + ',' , '' ) + QUOTENAME(ProductType) FROM ( SELECT DISTINCT ProductType FROM tblAgentCommission) AS ProductType SELECT @PivotProductType --Create the dynamic query with all the values for --pivot column at runtime SET @Query = N 'SELECT AgentID, ' + @PivotProductType + ' FROM [dbo].[tblAgentCommission] PIVOT( SUM (CommissionRate) FOR ProductType IN ( ' + @PivotProductType + ' )) AS P' SELECT @Query --Execute dynamic query EXEC sp_executesql @Query END GO I used this SP in my gridview but it only shows the 1st column and gives this error: Server Error in '/' Application. A field or property with the name 'AgentID' was not found on the selected data source. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: A field or property with the name 'AgentID' was not found on the selected data source. Any help is much appreciated.

Jes Sie · Answer

Update: Thanks @Manish Kumar for pointing it out. My bad. Below are my sample data: And this is what I want to achieve:

Jes Sie · Answer

@Abhinav Abhishek, thanks for the reply. Below is the script of the table: CREATE TABLE [dbo].[tblAgentCommission]( [AgentID] [nvarchar](50) NULL , [ProductType] [nvarchar](50) NULL , [CommissionRate] [ decimal ](2, 2) NULL , [UserName] [nvarchar](50) NULL ) ON [ PRIMARY ] GO The script for the pivot has been provided above. Thanks for any assistance.

Abhinav Abhishek · Answer

please provide script for the same.