Need help creating a function or temp table for this SQL statement

Transact-SQL
1

Please take a look at this query and provide any suggestions as to turn it into a function. Alternatively putting it into a temp table would work too. Thanks!

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 Student_Name, ' + @cols +', total
FROM
(
select xxx.STUID, s.Student_Name, learningObjective, numberCorrect, total
from XXX
INNER JOIN
(
select STUID, SUM(total) as total

  	FROM
  	(
  	select  STUID, TESTCODE, learningObjective, numberCorrect ,
  	SUM(CASE WHEN numberCorrect > 3 then 1 else 0 end) as total

  	FROM XXX
  	where learningObjective <> ''Overall''
  	group by STUID, TESTCODE, learningObjective, numberCorrect
  	) 
  	a
  	group by STUID

)
z
ON XXX.STUID = z.STUID
INNER JOIN STU s ON XXX.STUID = s.PID
) src

pivot
(
max(numberCorrect) for learningObjective in (' + @cols + ')
) piv
'

execute(@query)

2

Maybe this will work for you:

declare @cols  nvarchar(max)=''
       ,@query nvarchar(max)=''
;

select @cols=@cols+case when @cols='' then '' else ',' end+quotename(learningobjective)
  from (select distinct learningobjective from xxx where learningobjective<>'Overall') as t
;

set @query='
select p.student_name
      ,'+@cols+'
      ,p.total
  into ##temp
  from (select x.stuid
              ,s.student_name
              ,x.learningobjective
              ,x.numbercorrect
              ,sum(1) over(partition by x.stuid) as total
          from xxx as x
               inner join stu as s
                       on s.pid=x.stuid
         where x.numbercorrect>3
       ) as s
 pivot (max(s.numbercorrect) for s.learningobjective in ('+@cols+')) as p
';

execute sp_executesql @query;

select * from ##temp;

drop table ##temp;
3

Thanks bitsmed, i given up on that a while back, as it turns out a new situation calls for something similar. Much appreciated i think i can work with this.

cheers!
-kris