Dynamically construct static columns using pivot in SQL Server

Question

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

I tried with pivot but can't accomplish the end result, plase suggest some with example

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

Script :

CREATE TABLE [dbo].[Master_Table]
(
    [Retailer_ID] [nvarchar](50) NULL,
    [RQSC_Survey_Name] [nvarchar](50) NULL,
    [RQSC_Date] [date] NULL,
    [RQSC_Weightage] [decimal](18, 0) NULL,
    [RQSC_Score] [decimal](18, 0) NULL
) ON [PRIMARY]   

INSERT [dbo].[Master_Table] ([Retailer_ID], [RQSC_Survey_Name], [RQSC_Date], [RQSC_Weightage], [RQSC_Score]) 
VALUES (N'198760', N'Quality', CAST(0x1C3B0B00 AS Date), CAST(10 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)))

INSERT [dbo].[Master_Table] ([Retailer_ID], [RQSC_Survey_Name], [RQSC_Date], [RQSC_Weightage], [RQSC_Score]) 
VALUES (N'198760', N'Quality', CAST(0x003B0B00 AS Date), CAST(10 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)))

INSERT [dbo].[Master_Table] ([Retailer_ID], [RQSC_Survey_Name], [RQSC_Date], [RQSC_Weightage], [RQSC_Score]) 
VALUES (N'198760', N'Quality', CAST(0xE13A0B00 AS Date), CAST(5 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)))

INSERT [dbo].[Master_Table] ([Retailer_ID], [RQSC_Survey_Name], [RQSC_Date], [RQSC_Weightage], [RQSC_Score]) 
VALUES (N'198760', N'Quality', CAST(0xC23A0B00 AS Date), CAST(10 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)))

My query :

SELECT
    Retailer_ID, RQSC_Survey_Name,
    [1] As LastVist, [2] as 'M-1', [3] as 'M-2', [4] as 'M-3',
    [1] as 'Score' 
FROM
    (SELECT 
         RQSC_Score, Retailer_ID, RQSC_Survey_Name, 
         RQSC_Weightage,
         ROW_NUMBER() OVER (PARTITION BY Retailer_ID ORDER BY RQSC_Date DESC) AS Rownumber 
     FROM 
         master_table 
     WHERE
         Retailer_ID = 198760 AND RQSC_Survey_Id = 298) src   
PIVOT(SUM(RQSC_Score)for Rownumber in ([1], [2], [3],[4])) piv;

Here if I select weightage column I am getting duplicate rows based upon weightage column but in expected result I should get weightage column

why retail id turned to 123 rom ABC – mohan111 Mar 30 '16 at 09:36 — mohan111, Mar 30 '16 at 09:36

score 0 · Answer 1 · answered Mar 30 '16 at 11:23

0

Sorry for the Delay I have occupied with work . Basing on your sample data we can achieve the using Stuff and Dynamic Sql

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'' 
FROM
    (
    SELECT 
         Retailer, SurveyName, Score, 
         Weights,
         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'

EXEC sp_executesql @statement = @statement

answered Mar 30 '16 at 11:23

mohan111

8,633
4
28
55

what it is exactly @Pradeep why you have posted this I have given you solution already – mohan111 Mar 30 '16 at 12:12
Wow that's great @mohan111 but at the end result i need weights column so i have selected weights column in below query so here comes the problem 1. when i select weights column i am getting two rows but only one row should come 2. Value of weights column should be 10 and not 5 why because as per current month weights value is 10 – Pradeep Mar 30 '16 at 12:15
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 – Pradeep Mar 30 '16 at 12:15
as per your static pivot query I have implemented Dynamic query so you have given expected result weight as 10 and it is assumed data and Assumed Query modify according to your requirement @Pradeep – mohan111 Mar 30 '16 at 12:19
As per your dynamic query the weight column was selected from #temp alone but weight column was missing in upper select so i am not getting weight values and when i try to select weight column in upper select i am getting two rows and the same difficulties i am facing in my static pivot query @mohan111 – Pradeep Mar 30 '16 at 12:31

Pradeep · Accepted Answer · 2016-04-08T17:49:32.297

I got the solution

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 ([1],[2],[3],[4])
) as p         
inner join cte as b on b.retailer=p.retailer and b.surveyname=p.surveyname    and b.rn=1  ;

Dynamically construct static columns using pivot in SQL Server

2 Answers2