Dynamic PIVOT query in SQL Server


Problem: Suppose we have a table TMP where we keep the data of Representatives like RepName, NumberOfLead, Date, etc. as below

Now we have to show data like below.
  
Answer: Very easy we can use the PIVOT function but it may create a problem when we add a new Representative. We have to change our Procedure when a new representative will be added. Don't worry one solution is here we can use PIVOT is a dynamic query like below. 

CREATE TABLE #TMP
(
       REPNAME VARCHAR(100),
       NUMBEROFLEAD INT,
       DATE DATETIME
)

INSERT INTO #TMP
SELECT 'DILIP',2,'11/11/2015'
UNION ALL
SELECT 'ANIL',4,'11/11/2015'
UNION ALL
SELECT 'PARAM',7,'11/11/2015'
UNION ALL
SELECT 'DILIP',6,'11/12/2015'
UNION ALL
SELECT 'ANIL',3,'11/12/2015'
UNION ALL
SELECT 'PARAM',5,'11/12/2015'

SELECT * FROM #TMP
DECLARE @COLS AS NVARCHAR(MAX),
    @QUERY  AS NVARCHAR(MAX);

SET @COLS = STUFF((SELECT DISTINCT ',' + QUOTENAME(TMP.REPNAME)
            FROM #TMP TMP
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @QUERY = 'SELECT DATE, ' + @COLS + ' FROM
            (
                SELECT REPNAME
                    , NUMBEROFLEAD
                    , DATE
                FROM #TMP
           ) X
            PIVOT
            (
                 MAX(NUMBEROFLEAD)
                FOR REPNAME IN (' + @COLS + ')
            ) P '


EXECUTE(@QUERY)

DROP TABLE #TMP


This query will return the result as above given pic.

Now you can try this with adding a new representative as below

SELECT 'MANISH',5,'11/12/2015'

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...