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'