PIVOT Made Easy

Introduction

Most of the developers made mistake in creation of PIVOT query as the syntax is little bit hazy. Here we are trying to make a Stored Procedure which can dynamically create Pivot Query by passing the parameters value only. Hope it will be informative and easy to implement.

Create Base Table and Make PIVOT in Regular Faison

Step-1 [ The Base Table ]

CREATE TABLE dbo.Products

(

  ProductID INT PRIMARY KEY,

  Name      NVARCHAR(255) NOT NULL UNIQUE

);

GO

INSERT dbo.Products VALUES

(1, N'foo'),

(2, N'bar'),

(3, N'kin');

GO

CREATE TABLE dbo.OrderDetails

(

  OrderID INT,

  ProductID INT NOT NULL

    FOREIGN KEY REFERENCES dbo.Products(ProductID),

  Quantity INT

);

GO

INSERT dbo.OrderDetails VALUES

(1, 1, 1),

(1, 2, 2),

(2, 1, 1),

(3, 3, 1);

Step-2 [ Make PIVOT ]

SELECT p.[foo], p.[bar], p.[kin]

FROM

(

  SELECT p.Name, o.Quantity

   FROM dbo.Products AS p

   INNER JOIN dbo.OrderDetails AS o

          ON p.ProductID = o.ProductID

) AS j

PIVOT

(

  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])

) AS p;

Output:

foo      bar      kin

2          2          1

Now Make the Dynamic Stored Procedure and Execute it

Step – 1 [ Create the Stored Procedure ]

IF OBJECT_ID(N'[dbo].[proc_Pivot]', N'P')IS NOT NULL

   DROP PROCEDURE [dbo].[proc_Pivot];

GO

CREATE PROCEDURE [dbo].[proc_Pivot]

       (

            @p_TableName        VARCHAR(Max),

            @p_TableCol         VARCHAR(Max),

            @p_Aggrigate        CHAR(10),

            @p_AggrigateCol     VARCHAR(50),

            @p_PivtCompCol      VARCHAR(50),

            @p_PivotColumns     VARCHAR(Max)

       )

AS

BEGIN

DECLARE @v_SQL               VARCHAR(Max),

        @v_PivotColumns      VARCHAR(Max),

        @v_PivotColumnsall   VARCHAR(Max);

SET @v_PivotColumns = 'pvt.[' + REPLACE(@p_PivotColumns, ',', '],pvt.[') + ']';

SET @v_PivotColumnsall = '[' + REPLACE(@p_PivotColumns, ',', '],[') + ']';

SET @v_SQL = 'SELECT ' + @v_PivotColumns + '

                FROM  (SELECT * FROM '+ @p_TableName + ') AS j

                PIVOT

                ( ' + @p_Aggrigate +'('+ @p_AggrigateCol +') FOR '+

                           @p_PivtCompCol + ' IN ( '+ @v_PivotColumnsall +') ) AS pvt';

EXEC(@v_SQL);

END

Step-2 [ Execute The Stored Procedure ]

EXEC [dbo].[proc_Pivot]

            @p_TableName        = 'view_ProductOrder',

            @p_TableCol         = 'Name,Quantity',

            @p_Aggrigate        = 'SUM',

            @p_AggrigateCol     = 'Quantity',

            @p_PivtCompCol      = 'Name',

            @p_PivotColumns     = 'foo,bar,kin';

Output:

foo      bar      kin

2          2          1

Hope you like it.

Posted by: MR. JOYDEEP DAS