在SQL Server中使用pivot动态构造静态列 [英] Dynamically construct static columns using pivot in SQL server
问题描述
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
如果我选择权重列，我会根据权重列获得重复行，但在预期结果中，我应该得到权重列和权重值
列应该是10而不是5为什么因为当前月份通过与日期列比较，权重值是10，我需要像预期结果那样单行
我尝试了什么：
我的查询：
IF object_id（'tempdb ..＃Temp'）不为空
DROP表#TEMP
创建表#Temp
（零售商varchar（10），SurveyName varchar（10） ），日期日期时间，分数int，权重int）
;
插入#Temp
（零售商） ，SurveyName，日期，分数，权重）
VALUES
（'198760'，'Quality'，'2016-06-03 05:30:00'，10， 10），
（'198760'，'质量'，'2016-06-02 05:30:00'，5,10），
（'198760' ，'质量'，'2016-06-01 05:30:00'，5,5），
（'198760'，'质量'，'2015-06-12 05:30： 00'，10,10）
;
DECLARE @statement NVARCHAR（最大值）
，@ columns NVARCHAR（最大），
@col NVARCHAR（最大）
SELECT @columns =
STUFF（（SELECT distinct']，['+
CAST（ROW_NUMBER（）OVER（按零售商划分ORDER BY日期DESC）AS VARCHAR（50））AS Rownumber
FROM #Temp
FOR XML PATH（''），类型
）.value（'。'，'NVARCHAR（MAX）'）
，1,2，''）+']'
SELECT @statement ='SELECT
零售商，SurveyName，
MAX（[1]）作为LastVist，MAX（[2] ）作为''M-1''，MAX（[3]）为''M-2''，MAX（[4]）为''M-3''，
MAX（ [1]）作为''得分''，重量，日期
来自
（
SELECT
零售商，SurveyName，分数，
重量，日期，
ROW_NUMBER（）OVER（按零售商分类ORDER BY日期DESC）作为Rownumber
FROM
#Temp
）src
PIVOT（Rownumber的SUM（得分）（'+ @columns + '））as pvt
GROUP BY零售商，SurveyName，权重，日期'
EXEC sp_executesql @statement = @statement
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
推荐答案
我得到了解决方案
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 ;
这篇关于在SQL Server中使用pivot动态构造静态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
