应用PIVOT运算符时出错

Question

我必须对一些表应用联接，这些表具有用户以问题形式进行的评估的一些细节。有些评估有10个问题，而另一些评估可能只有5个问题。问题的答案需要显示为列而不是行。我准备了以下查询：

IF OBJECT_ID(N'tempdb..#VALS') IS NOT NULL 
BEGIN
DROP TABLE #VALS
END
GO
DECLARE @SQL NVARCHAR(MAX)
DECLARE @VALS NVARCHAR(MAX)
SELECT DISTINCT SUBSTRING('QuestionText', 1, 100) AS Vals
INTO #VALS FROM Analytics.DimEvaluation
SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS
SET @SQL = 'SELECT top 10
Act.ActivityCode as [Region]
,Act.ActivityType AS [Activity Type]
,Act.ActivityCode AS [Activity Code]
,Act.ActivityName AS [Activity Name]
,CONVERT(DATETIME, Act.StartDate, 102) AS [Activity Start Time]
,CONVERT(DATETIME, Act.EndDate, 102) AS [Activity End Time]
,DimLoc.FacilityName AS [Facility]
,DimLoc.FacilityCity + '''' + DimLoc.FacilityCountry AS [Facility City, Facility Country]
,Act.EstimatedCreditHours AS [Estimated Credit Hours]
,REPLACE(Emp.ImportKey, '','', '''') AS [User Global ID]
,(SELECT NoteText FROM PERSON WITH (NOLOCK) WHERE PersonPk = emp.SourceID) AS [Local Employee Number]
,Emp.FirstName AS [First Name]
,Emp.LastName AS [Last Name]
,Emp.FirstName + '''' + Emp.LastName AS [User Full Name]
,Emp.Email AS [User Email]
,'+@VALS+' 
AS [STTEST]
FROM Analytics.DimActivity AS Act WITH (NOLOCK)
INNER JOIN Analytics.FactActivityLocation factResLoc WITH (NOLOCK) ON factResLoc.DimActivityId = Act.DimActivityId
LEFT OUTER JOIN Analytics.DimLocation DimLoc WITH (NOLOCK) ON DimLoc.DimLocationId = factResLoc.DimLocationId
INNER JOIN Analytics.FactActivityAttempt FACT WITH (NOLOCK) ON fact.DimActivityId = Act.DimActivityId
INNER JOIN Analytics.DimEmployee Emp WITH (NOLOCK) ON Emp.DimEmployeeID = fact.Dimemployeeid
inner join Analytics.FactEvaluation FactEval WITH (NOLOCK) on FactEval.DimActivityId = Act.DimActivityId
inner join Analytics.DimEvaluation DimEval WITH (NOLOCK) on FactEval.DimEvaluationId = DimEval.DimEvaluationId
PIVOT(MIN([Answer]) FOR [QuestionText] IN ('+@VALS+')) PIV'
PRINT @SQL
EXEC(@SQL)

但在执行时，我得到以下错误：

(1 row(s) affected)
SELECT top 10 Act.ActivityCode as [Region]
,Act.ActivityType AS [Activity Type]
,Act.ActivityCode AS [Activity Code]
,Act.ActivityName AS [Activity Name]
,CONVERT(DATETIME, Act.StartDate, 102) AS [Activity Start Time]
,CONVERT(DATETIME, Act.EndDate, 102) AS [Activity End Time]
,DimLoc.FacilityName AS [Facility]
,DimLoc.FacilityCity + '' + DimLoc.FacilityCountry AS [Facility City, Facility Country]
,Act.EstimatedCreditHours AS [Estimated Credit Hours]
,REPLACE(Emp.ImportKey, ',', '') AS [User Global ID]
,(SELECT NoteText FROM PERSON WITH (NOLOCK)     WHERE PersonPk = emp.SourceID) AS [Local Employee Number]
,Emp.FirstName AS [First Name]
,Emp.LastName AS [Last Name]
,Emp.FirstName + '' + Emp.LastName AS [User Full Name]
,Emp.Email AS [User Email]
,[QuestionText] 
AS [STTEST]
FROM Analytics.DimActivity AS Act WITH (NOLOCK)
INNER JOIN Analytics.FactActivityLocation factResLoc WITH (NOLOCK) ON factResLoc.DimActivityId = Act.DimActivityId
LEFT OUTER JOIN Analytics.DimLocation DimLoc WITH (NOLOCK) ON DimLoc.DimLocationId = factResLoc.DimLocationId
INNER JOIN Analytics.FactActivityAttempt FACT WITH (NOLOCK) ON fact.DimActivityId = Act.DimActivityId
INNER JOIN Analytics.DimEmployee Emp WITH (NOLOCK) ON Emp.DimEmployeeID = fact.Dimemployeeid
inner join Analytics.FactEvaluation FactEval WITH (NOLOCK) on FactEval.DimActivityId = Act.DimActivityId
inner join Analytics.DimEvaluation DimEval WITH (NOLOCK) on FactEval.DimEvaluationId = DimEval.DimEvaluationId
PIVOT(MIN([Answer]) FOR [QuestionText] IN ([QuestionText])) PIV
Msg 265, Level 16, State 1, Line 35
The column name "QuestionText" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 35
The column 'DimActivityId' was specified multiple times for 'PIV'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Act.ActivityCode" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "Act.ActivityType" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "Act.ActivityCode" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "Act.ActivityName" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "Act.StartDate" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "Act.EndDate" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "DimLoc.FacilityName" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "DimLoc.FacilityCity" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "DimLoc.FacilityCountry" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "Act.EstimatedCreditHours" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "Emp.ImportKey" could not be bound.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "emp.SourceID" could not be bound.
Msg 4104, Level 16, State 1, Line 19
The multi-part identifier "Emp.FirstName" could not be bound.
Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "Emp.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "Emp.FirstName" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "Emp.LastName" could not be bound.
Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "Emp.Email" could not be bound.

需要帮助识别错误并解决它。提前谢谢。

Answer 1

您显然想要QuestionText列中的不同值，而不是其名称

..
SELECT DISTINCT SUBSTRING(QuestionText, 1, 100) AS Vals
INTO #VALS 
FROM Analytics.DimEvaluation;
..

BTWNOLOCK，希望你知道你在做什么。如果当时正在运行任何更新，则可能会得到不一致的结果。