Oct 4, 2015

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 PIVOT function but it may create a problem when we add new Representative. We have to change our Procedure  when a new representative will be add. Don't worry one solution is here we can use PIVOT in 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 result as above given pic..
Now you can try this with adding new representative as below

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