Dynamically construct static columns using pivot in SQL server

Question

2.33/5 (2 votes)

See more:

SQL-Server-2008R2

C#

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

SQL

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

Posted 30-Mar-16 5:57am

Member 12423909

Updated 30-Mar-16 22:42pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 12423909 · Accepted Answer · 2016-03-30T22:42:00

I got the solution

C#

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  ;