CREATE PROC [dbo].[sp_STUD1_GetReportPrepEnglish]
(
	@year INT
)
AS
--DECLARE @year INT = 2017
DECLARE @cols AS NVARCHAR(MAX),
		@colsName AS NVARCHAR(MAX),
		@query  AS VARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Prep2017_SessionReviewTool_Date +'_'+c.col) 
                      from qt_PrepProgramme2017
                      cross apply 
                      (
                        select 'Prep2017_SessionReviewTool_EnglishResult' col
                        union all
                        select 'Prep2017_SessionReviewTool_EnglishNote'
                      ) c
					WHERE	(Prep2017_SessionReviewTool_EnglishResult <> '' AND Prep2017_SessionReviewTool_EnglishResult <> '0')
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsName 
    = STUFF((SELECT ', MAX(' + QUOTENAME(Prep2017_SessionReviewTool_Date +'_'+c.col) 
               +') as ['
               + Prep2017_SessionReviewTool_Date + CASE WHEN c.col = 'Prep2017_SessionReviewTool_EnglishResult' THEN ' Result]' ELSE ' Notes]' END
             from qt_PrepProgramme2017
             cross apply 
             (
                select 'Prep2017_SessionReviewTool_EnglishResult' col
                union all
                select 'Prep2017_SessionReviewTool_EnglishNote'
             ) c
			 WHERE	(Prep2017_SessionReviewTool_EnglishResult <> '' AND Prep2017_SessionReviewTool_EnglishResult <> '0')
			 GROUP BY ', MAX(' + QUOTENAME(Prep2017_SessionReviewTool_Date +'_'+c.col) 
					+') as ['
					+ Prep2017_SessionReviewTool_Date + CASE WHEN c.col = 'Prep2017_SessionReviewTool_EnglishResult' THEN ' Result]' ELSE ' Notes]' END
					,c.col
					,Prep2017_SessionReviewTool_Date
			ORDER BY CASE WHEN c.col = 'Prep2017_SessionReviewTool_EnglishResult' THEN Prep2017_SessionReviewTool_Date+'1' ELSE Prep2017_SessionReviewTool_Date+'2' END			 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = '
SELECT	ItemId ,
		Prep2017_SessionReviewTool_PrepProgrammeYear [Year],
        Prep2017_SessionReviewTool_Name [Name],
        Prep2017_SessionReviewTool_Surname [Surname],
        Prep2017_SessionReviewTool_School [School],
		' + @colsName + ' ,
		MAX(dbo.fn_STUD_1_GetPrepEnglishAveragePrecentage(Prep2017_SessionReviewTool_Name,Prep2017_SessionReviewTool_Surname,Prep2017_SessionReviewTool_PrepProgrammeYear)) [Average %] ,
		MAX(dbo.fn_STUD_1_GetPrepEnglishTeachers(Prep2017_SessionReviewTool_Name,Prep2017_SessionReviewTool_Surname,Prep2017_SessionReviewTool_PrepProgrammeYear)) [Teacher]
FROM 
     (
      SELECT ItemId ,
        Prep2017_SessionReviewTool_PrepProgrammeYear ,
		Prep2017_SessionReviewTool_Name ,
		Prep2017_SessionReviewTool_Surname ,
		Prep2017_SessionReviewTool_School ,
        Prep2017_SessionReviewTool_Date+''_''+col col, 
        value
      FROM
      (
        SELECT  I.recId ItemId ,
				Prep2017_SessionReviewTool_PrepProgrammeYear ,
				Prep2017_SessionReviewTool_Name ,
				Prep2017_SessionReviewTool_Surname ,
				Prep2017_SessionReviewTool_School ,
				Prep2017_SessionReviewTool_Date ,
				Prep2017_SessionReviewTool_EnglishResult ,
				Prep2017_SessionReviewTool_EnglishNote 
        FROM	qt_PrepProgramme2017
				CROSS APPLY (SELECT splitdata FROM dbo.SplitString(surfaceLinkItems, '','')) X 
				JOIN dbo.pal_SurfaceItem I
					ON I.surfaceId = (SELECT recId FROM pal_Surface WHERE name = ''Profiles'')
					AND I.recId = X.splitdata
		WHERE	Prep2017_SessionReviewTool_PrepProgrammeYear = '+CAST(@year AS VARCHAR(4))+'
      ) src
      unpivot
      (
        value
        for col in (Prep2017_SessionReviewTool_EnglishResult,Prep2017_SessionReviewTool_EnglishNote)
      ) unpiv
     ) s
     pivot 
     (
       max(value)
       for col in (' + @cols + ')
     ) p 
GROUP BY ItemId ,
		Prep2017_SessionReviewTool_PrepProgrammeYear ,
        Prep2017_SessionReviewTool_Name ,
        Prep2017_SessionReviewTool_Surname ,
        Prep2017_SessionReviewTool_School
ORDER BY Prep2017_SessionReviewTool_Surname ,
		Prep2017_SessionReviewTool_Name'


PRINT @query
EXEC(@query)



GO


