Dynamically construct static columns using pivot in SQL server

See more:

SQL

pivot

T-SQL

SQL-Server-2008R2

I have a master table which doesn't contain these columns (Visit_date, M-1, M-2, M-3) but in end result I need these columns (Visit_date, M-1, M-2, M-3) along with existing master table columns

I tried with pivot but can't accomplish the end result, please edit the below query

Master table:

RetailerID Retailer SurveyName    Date     Score   Weights
  198760     ABC      Quality  06/03/2016   10       10
  198760     ABC      Quality  06/02/2016   5        10
  198760     ABC      Quality  06/01/2016   5        5
  198760     ABC      Quality  06/12/2015   10       10

Expected Result :

Retailer_id  survery_Name    Last_Visit    M-1    M-2    M-3   Weightage
    198760     quality          10          5       5     10     10 

Here if I select weightage column I am getting duplicate rows based upon weightage column but in expected result I should get weightage column and Value of weightage
column should be 10 and not 5 why because as per current month weightage value is 10 by comparing with date column and i need in single row like Expected result

What I have tried:

My Query:

IF object_id('tempdb..#Temp') is not null
DROP TABLE #TEMP


CREATE TABLE #Temp
(Retailer varchar(10), SurveyName varchar(10), Date datetime, Score int, Weights int)
;

INSERT INTO #Temp
(Retailer, SurveyName, Date, Score, Weights)
VALUES
('198760', 'Quality', '2016-06-03 05:30:00', 10, 10),
('198760', 'Quality', '2016-06-02 05:30:00', 5, 10),
('198760', 'Quality', '2016-06-01 05:30:00', 5, 5),
('198760', 'Quality', '2015-06-12 05:30:00', 10, 10)
;

DECLARE @statement NVARCHAR(max)
,@columns NVARCHAR(max),
@col NVARCHAR(max)


SELECT @columns =
STUFF((SELECT distinct '],[' +
CAST(ROW_NUMBER() OVER (PARTITION BY Retailer ORDER BY Date DESC) AS VARCHAR(50)) AS Rownumber
FROM #Temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 2, '')+ ']'

SELECT @statement = 'SELECT
Retailer, SurveyName,
MAX([1]) As LastVist, MAX([2]) as ''M-1'', MAX([3]) as ''M-2'', MAX([4]) as ''M-3'',
MAX([1]) as ''Score'',weights,date
FROM
(
SELECT
Retailer, SurveyName, Score,
Weights,date,
ROW_NUMBER() OVER (PARTITION BY Retailer ORDER BY Date DESC) AS Rownumber
FROM
#Temp

) src
PIVOT(SUM(Score)for Rownumber in (' + @columns + ')) as pvt
GROUP BY Retailer, SurveyName,weights,date'

EXEC sp_executesql @statement = @statement