Sum and count aggregate functions in dynamic pivot table

Question

I have a Dynamic PIVOT query in which Columns are dynamically generated.

My table: ATTENDANCE_MASTER Contains: ID, Stud_id, ATT_DATE, PRESENT

which stores data like:

ID  Stud_id ATT_DATE   PRESENT
1     1     2015-08-1    1
2     2     2015-08-1    0
3     3     2015-08-1    1
4     1     2015-08-2    0
5     2     2015-08-2    1
6     3     2015-08-2    1

I need result like this

Stud_ID  2015-08-01   2015-08-2 2015-08-3 Total  Count
1            1            0         1      2      3
2            1            1         1      3      3
3            1            1         0      2      3
4            0            0         1      1      3

I have created PIVOT Query

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns1 NVARCHAR(MAX);   

SELECT  @columns = N'',@columns1 =N'';
SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.Att_Date, 111), '/', '-'))
                FROM  ATTENDANCE_MASTER AS p
                GROUP BY p.ATT_DATE
                ORDER BY p.ATT_DATE
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');

SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + ', Total = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + '
, Outof = ' + STUFF(REPLACE(@columns1, ', p1.[', ' + p1.['), 1, 4, '') + '
FROM
(
  SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM ATTENDANCE_MASTER AS p
) AS j
PIVOT
(
  SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;
PIVOT
(
  Count(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns1, ', p1.[', ',['), 1, 1, '') + ')
) AS p1;';
PRINT @sql;
EXEC sp_executesql @sql;

I tried lot, but there is no solutions. Can I use two aggregate functions in this query? Please suggest me solution.

Thanks in advance.

Answer 1

You can achieve this using Common Table Expression. For example your table is

create table [student_Register]
(
ID int not null unique identity(1,1),
  Stud_id int not null ,
   ATT_DATE varchar(50) not null,
     PRESENT int not null
)

And Values

insert into student_Register
select 1, '01-08-2015', 1 union all
select 2, '01-08-2015', 0 union all
select 3, '01-08-2015', 0 union all
select 1, '02-08-2015', 0 union all
select 2, '02-08-2015', 0 union all
select 3, '02-08-2015', 1 union all
select 1, '03-08-2015', 1 union all
select 2, '03-08-2015', 0 union all
select 3, '03-08-2015', 0 union all
select 1, '04-08-2015', 0 union all
select 2, '04-08-2015', 0 union all
select 3, '04-08-2015', 1 UNION ALL
select 1, '05-08-2015', 1 union all
select 2, '05-08-2015', 0 union all
select 3, '05-08-2015', 0 

query for get ATT_DATE Value In single String

DECLARE @cols AS NVARCHAR(MAX)
DECLARE  @query  AS NVARCHAR(MAX)
DECLARE @SEPERATOR as VARCHAR(1) 
DECLARE @SP  INT
DECLARE @VALUE VARCHAR(MAX)
DECLARE @INSTR VARCHAR(MAX)
DECLARE @ORGSTR VARCHAR(MAX)
DECLARE @COLSREV VARCHAR(MAX)


SET @SEPERATOR = ','
SET @COLSREV = ''

--use stuff you can get full value of ATT_Date As single string

SET @ORGSTR = STUFF((SELECT distinct ',' + QUOTENAME([ATT_DATE]) 
            FROM dbo.student_Register 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @INSTR = ''+@ORGSTR +','

Here insert MIn() fun to every ATT_Date Value

----------LOOP FOR INSERT MIN() FUNCTION IN TO THE INTSQR
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)  --LENGTH OF FIRST STRING SET INCLUDE COMMA
    SELECT  @COLS = LEFT(@INSTR , @SP - 1)              -- TAKE FIRST SET
    SET @COLS =  'min('+@COLS+') AS '+@COLS+'';         --ADD FUN IN FIRST SET
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')           -- REMOVE FIRST SET STRING FROM @INSTR
    --INSERT INTO #tempTab (id) VALUES (@COLS)
   SET @COLSREV +=''+@COLS+','
  END ----LOOP END

  set @COLSREV = STUFF(@COLSREV,LEN(@COLSREV),1,'')

MAIN DYNAMIC QUERY WITH PIVOT

set @query = 'SELECT  Stud_id, '+@COLSREV+'   from(  select * from student_Register  ) 
    x 
        pivot 
        (
         min([PRESENT])

for [ATT_DATE] in ('+@ORGSTR+')
) As p


  group by [Stud_id] '

Finally Use CTE merge Pivot query with [DAYS OF PRESENT] and [Total Days]

set @query = 'with mycte
    As
    (

    '+@query+'

    ),
     mycte1
    As(
    SELECT Stud_id, COUNT(PRESENT) AS [TOTAL DAYS], SUM(PRESENT) AS [DAYS OF PRESENT] FROM student_Register GROUP BY Stud_id
    )

    select mycte1.Stud_id, '+@ORGSTR+' , mycte1.[TOTAL DAYS], mycte1.[DAYS OF PRESENT] from mycte
left join mycte1 on mycte.Stud_id = mycte1.Stud_id'

    execute sp_executesql @query;

Here My Fiddle Sample: Dynamic_String With Pivot