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'

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...