Sql-server – Group by other table with sum function

Take your most complex (most GROUP BY columns) then use the OVER clause

SELECT
   school, board, col3, col4, ...,
   cnt_whoPassedA, ...,
   SUM(cnt_whoPassedA) OVER (PARTITION BY board) AS cnt_whoPassedA_ByBoard,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school) AS cnt_whoPassedA_BySchool,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school, board) AS cnt_whoPassedA_BySchoolBoard,
   ...
FROM
(
select 
 school, 
 board, 
 col3, col4, ...
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board, A.col3, A.col4...
) T

However, I would consider doing this in my report layer (say in SSRS): do you need all levels of grouping at the same time?

The table-valued function requires static columns (names and types). If you want dynamic columns in result-set, use procedure instead of function.

E.G.

Create Procedure X(@tables varchar(50)) as
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)  


        DECLARE oCur CURSOR FOR 
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56' 

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END 

            SET @ssql =  left (@ssql,len(@ssql)-3)   
            set @sql = 'select * from '+@tables+' where ' + @ssql 
            exec(   @sql)

           close ocur;
        deallocate ocur;
GO
X 'Tree'