Untitled

ALTER PROCEDURE GET_REPORT_UEI_YTD 
    @PageNumber INT = 1, 
    @PageSize INT = 10
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cols NVARCHAR(MAX);
    DECLARE @query NVARCHAR(MAX);
    DECLARE @offset INT = (@PageNumber - 1) * @PageSize;

    -- Get dynamic columns based on FIELD_QUESTION values
    SELECT @cols = STUFF(
        (SELECT DISTINCT ',' + QUOTENAME(FIELD_QUESTION)
         FROM REPORT_UEI_D_YTD 
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    -- Build dynamic query with PIVOT and Total_Sum column
    SET @query = '
    SELECT *, 
           ' + STUFF(
        (SELECT '+ ISNULL(' + QUOTENAME(FIELD_QUESTION) + ', 0)' 
         FROM (SELECT DISTINCT FIELD_QUESTION FROM REPORT_UEI_D_YTD) AS Q
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ' ') + 
        ' AS Total_Sum
    FROM (
        SELECT 
            H.REPORT_YTD_H_ID,
            H.CABANG AS BRANCH,
            H.AGREEMENT,
            D.FIELD_QUESTION,
            TRY_CAST(D.FIELD_ANSWER AS FLOAT) AS FIELD_ANSWER
        FROM REPORT_UEI_H_YTD H
        JOIN REPORT_UEI_D_YTD D 
          ON H.REPORT_YTD_H_ID = D.REPORT_YTD_H_ID
    ) AS SourceTable
    PIVOT (
        MAX(FIELD_ANSWER) 
        FOR FIELD_QUESTION IN (' + @cols + ')
    ) AS PivotTable
    ORDER BY REPORT_YTD_H_ID 
    OFFSET ' + CAST(@offset AS NVARCHAR(10)) + ' ROWS 
    FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS ONLY;';

    -- Debugging: print the generated query
    PRINT @query;

    -- Execute the final dynamic SQL query
    EXEC(@query);
END;
GO