Dynamic query for static columns

Transact-SQL
1

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

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

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

Here if I select weightage column I am getting duplicate rows which is based upon weightage column value 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 everthing in single row like Expected result

2

I don't see the purpose of making this a dynamic sql, since you "fix" the columns when you name them "lastvisit", "m-1", "m-2", "m-3". What if there were more/less months? What if you have data for december, january and march, but no data for february?

This might do some of the thing you are looking for:

set @statement='
with cte
  as (select retailer
            ,surveyname
            ,max([date])
                 over(partition by retailer,surveyname)
             as [date]
            ,score
            ,weights
            ,row_number()
                 over(partition by retailer,surveyname
                      order by [date] desc
                     )
             as rn
        from #temp
     )
select p.retailer
      ,p.surveyname
      ,p.[1] as lastvisit
      ,p.[2] as [m-1]
      ,p.[3] as [m-2]
      ,p.[4] as [m-3]
      ,b.score
      ,b.weights
      ,b.[date]
  from (select retailer
              ,surveyname
              ,score
              ,rn
          from cte
       ) as a
 pivot (sum(score) for rn in ('+@columns+')) as p
       inner join cte as b
               on b.retailer=p.retailer
              and b.surveyname=p.surveyname
              and b.rn=1
;'
3

Wow thats great @bitsmed your query fulfilled my requirement:slight_smile::slight_smile::slight_smile: