Hello SQLteam! I have two questions.
i have a table where i am dynamically creating column names for the purpose of pivoting records. One thing i think worth mentioning is that although the column names are dynamic the number of them will always be five. I need to add a sixth column from a case statement based on the values of these dynamic columns. In my statement i am using "[dynamic column]" to represent the columns in question. So i need help referencing these columns for my case statement.
I am trying to use a parameter "@SCID", how would i assign a value to it considering it is within a parameter itself "@query"? In C# code behind i would typically do something like .Parameters.Add(new SqlParameter("@SCID", SCID)); Would that work the same here considering the layer its under?
DECLARE @cols AS NVARCHAR(MAX)='', @query AS NVARCHAR(MAX)=''; SELECT @cols = @cols + QUOTENAME(learningObjective) + ',' FROM (select distinct learningObjective from XXX where learningObjective <> 'Overall') as tmp select @cols = substring(@cols, 0, len(@cols)) set @query = 'SELECT *, (CASE WHEN [dynamic column1] > 3 then 1 else 0 END) + (CASE WHEN [dynamic column2] > 3 then 1 else 0 END) + (CASE WHEN [dynamic column3] > 3 then 1 else 0 END) + (CASE WHEN [dynamic column4] > 3 then 1 else 0 END) + (CASE WHEN [dynamic column5] > 3 then 1 else 0 END) as Number_Proficient from ( select STUID, learningObjective, numberCorrect from XXX where SCID = @SCID ) src pivot ( max(numberCorrect) for learningObjective in (' + @cols + ') ) piv group by STUID, [dynamic column1], [dynamic column2], [dynamic column3], [dynamic column4], [dynamic column5]' execute(@query)