Select dynamic column name from a PIVOT table

Transact-SQL
1

Hello SQLteam! I have two questions.

  1. 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.

  2. 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?

Thank you,

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)
2

Is there a way i could split the 4 columns into separate parameters then call them that way?

3

Thanks anyway i was able to do it by calculating the values before i pivoted, then joining the select statement to the pivot query.