Pivot in SQL Server 2014

Jes Sie

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)

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ')'.

Thanks in Advance.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar9y ago
Unfortunatley, am not able to help you on dot net side as am working as DBA.
Jes Sie9y ago
@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.
Manish Kumar9y ago
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
Jes Sie9y ago
Update:
Thanks @Manish Kumar for pointing it out. My bad. Below are my sample data:

And this is what I want to achieve:
Manish Kumar9y ago
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
Jes Sie9y ago
@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 Abhishek9y ago
please provide script for the same.

Agent ID	ProductionType	CommisionRate	UserName
User1	Motor	0.20	Admin