SQLTeam.com | Weblogs | Forums

Select dynamic column name from a PIVOT table

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,


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 = 
(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

select STUID, learningObjective, numberCorrect from XXX
	where SCID = @SCID
) src 
    max(numberCorrect) for learningObjective in (' + @cols + ')
  group by STUID, [dynamic column1],  [dynamic column2], [dynamic column3], [dynamic column4], [dynamic column5]'


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

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