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.
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