Dynamically Construct new Static Column in a Sql server 2008 Query

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 such as Retailer_id,survey_id,Group_Id,survery_Name where Last_visit column should contain score of RowID = 1,M-1 column should contain score of RowID = 2, M-2 column should contain score of RowID = 3,M-3 column should contain score of RowID = 4

I tried with pivot but can't accomplish the end result,Suggest me some with example
Master Table:

  RowID   Retailer    Group     Date            Score   Weights
     1     ABC      Quality  06/03/2016          10         10
     2     ABC      Quality  06/02/2016           5         10
     3     ABC      Quality  06/01/2016           5         5
     4     ABC      Quality  06/12/2015           10        10

End Result :slight_smile:

  Retailer_id  survey_id  Group_Id    survery_Name     Last_Visit  M-1  M-2  M-3

   123          143                1     quality         10         5    5    10
2

where are you getting these columns from?

Retailer_id survey_id Group_Id survery_Name

3

I am getting these columns from master table(Retailer_id, survey_id ,Group_Id ,survery_Name)
as its a flat table so it contains many columns,As per master table score is row wise but i need score values as new column such as Last_Visit ,M-1 ,M-2 ,M-3 in a single row