I need help with creating a report for a survey application. There are 5 main tables involved:
Every time a survey is created, a new ReportColumnName is generated that will need to be a column in the report. For each of these columns, and for each section (that is active in a survey), there is going to be a SectionScoreValue. This is linked further and checked whether it's inside an interval from ScoreCategories, and a resulting category (CategoryName) is shown for each survey and section.
I've been looking for days now on how to properly pivot these columns and show the needed values properly. By making an adaptation to some code got from a website, I thought this was going to work:
declare @SQL nvarchar(max), @Cols nvarchar(max)
select @Cols =
stuff(
(
select ', ' + quotename(dt)
from
(
select TOP 100 PERCENT Sv.ID, Sv.ReportColumnName as dt
from Surveys AS Sv
ORDER BY Sv.ID
) X
ORDER BY X.ID FOR XML PATH('')
),1,1,''
)
SELECT @Cols
set @SQL =
'SELECT Sv.ReportColumnName, SSSV.*
FROM Surveys AS Sv
INNER JOIN Sections_Surveys__ScoreValues AS SSSV
ON SSSV.FK_Surveys_ID = Sv.ID
INNER JOIN Sections AS Sc
ON Sc.ID = SSSV.FK_Sections_ID
INNER JOIN Sections_Surveys AS SS
ON SS.FK_Surveys_ID = Sv.ID
AND SS.FK_Sections_ID = Sc.ID
INNER JOIN ScoreCategories AS ScoreC
ON ScoreC.ID = SSSV.FK_ScoreCategories_ID
PIVOT (max(SSSV.SectionScoreValue) FOR Sv.ReportColumnName IN (' + @Cols
+')) pvt'
execute (@sql)
The @Cols variable holds the columns I need (i.e. the surveys' names) and it displays them properly (and delimited by a comma). So this part is working fine.
My issue is with the next part that should have put (initially) the SectionScoreValue for each SectionName (on the first column), and each of the surveys (each one on a separate column).
These surveys columns are added dynamically upon creation of a new one, so they are never a constant number.
Each of these tables has also a CreateDate column that has a default to GetDATE(). The errors I'm getting are:
The column 'CreateDate' was specified multiple times for 'pvt'.
The multi-part identifier "Sv.ReportColumnName" could not be bound.
The column prefix 'SSSV' does not match with a table name or alias name used in the query.
Please help in properly displaying my data. It should show something like:
Section Survey1 Survey2 Survey3 ......
s1 1.33 1.66 2.5
s2 0.00 3.33 4
s3 2.33 2 1.66
s4 0.66 2.5 3
The numbers can also be replaced with a value from ScoreCategoryName (like 'Good', 'Excellent' etc, according to the interval in which the number resides).
Thank you
EDIT 1: this is what PRINT @SQL yields:
SELECT Sv.ReportColumnName, SSSV.*
FROM Surveys AS Sv
INNER JOIN Sections_Surveys__ScoreValues AS SSSV
ON SSSV.FK_Surveys_ID = Sv.ID
INNER JOIN Sections AS Sc
ON Sc.ID = SSSV.FK_Sections_ID
INNER JOIN Sections_Surveys AS SS
ON SS.FK_Surveys_ID = Sv.ID
AND SS.FK_Sections_ID = Sc.ID
INNER JOIN ScoreCategories AS ScoreC
ON ScoreC.ID = SSSV.FK_ScoreCategories_ID
PIVOT (max(SSSV.SectionScoreValue) FOR Sv.ReportColumnName
IN ( [S1-1-2 -3], [S217], [al 3], [S37], [R4], [ult_nume_col])) pvt
EDIT 2: thanks to Giorgi I managed to get to a solution! Here's the final code that displays the columns exactly how I needed them:
declare @SQL nvarchar(max), @Cols nvarchar(max)
select @Cols =
stuff(
(
select ', ' + quotename(dt)
from
(
select TOP 100 PERCENT Sv.ID, Sv.ReportColumnName as dt
from Surveys AS Sv
ORDER BY Sv.ID
) X
ORDER BY X.ID FOR XML PATH('')
),1,1,''
)
SELECT @Cols
SET @SQL = ';
WITH cte
AS ( SELECT Sv.CreateDate, Sv.ReportColumnName, Sc.SectionName, SSSV.SectionScoreValue
FROM Surveys AS Sv
INNER JOIN Sections_Surveys__ScoreValues AS SSSV
ON SSSV.FK_Surveys_ID = Sv.ID
INNER JOIN Sections AS Sc
ON Sc.ID = SSSV.FK_Sections_ID
INNER JOIN Sections_Surveys AS SS
ON SS.FK_Surveys_ID = Sv.ID
AND SS.FK_Sections_ID = Sc.ID
INNER JOIN ScoreCategories AS ScoreC
ON ScoreC.ID = SSSV.FK_ScoreCategories_ID
)
SELECT SectionName , ' + @Cols + '
FROM cte PIVOT ( MAX(cte.SectionScoreValue) FOR cte.ReportColumnName IN (' + @Cols + ') ) pvt'
print @sql
execute (@sql)
Thanks for all the help guys, and for the "vote down" (if I could get to a result on my own I wouldn't have posted here).
Always use table expressions when dialing with pivoting. Something like this:
SET @SQL = ';
WITH cte
AS ( SELECT SSSV.SectionScoreValue ,
Sv.ReportColumnName ,
Section
FROM Surveys AS Sv
INNER JOIN Sections_Surveys__ScoreValues AS SSSV ON SSSV.FK_Surveys_ID = Sv.ID
INNER JOIN Sections AS Sc ON Sc.ID = SSSV.FK_Sections_ID
INNER JOIN Sections_Surveys AS SS ON SS.FK_Surveys_ID = Sv.ID AND SS.FK_Sections_ID = Sc.ID
INNER JOIN ScoreCategories AS ScoreC ON ScoreC.ID = SSSV.FK_ScoreCategories_ID
)
SELECT Section , ' + @Cols + '
FROM cte PIVOT ( MAX(SSSV.SectionScoreValue) FOR Sv.ReportColumnName IN (' + @Cols + ') ) pvt'
Also you can look at my answer on similar question:
SQL Pivot query based on "SUM of a column divided by SUM of another column"