How to get student attendance_status by date using sql query?

In my database I have this data. Now I want to query and fetch data based on date and group by roll_number. I am able to fetch all the data using

select * from attendance_records where intake='36-1' 

But I am not able to find what I want. I am learning databases and SQL. I used group by option like below but my result is not what I want. Please let me know how I get that result which I added in the 2nd picture. It will be a good help in my database learning.

select * from attendance_records where intake='36-1' group by date

But above query only give one student info. I could not find what I want.

1st pic: My Database Table

2nd pic: I want this result using SQL query

Answer

This works:

SELECT * INTO #attendance_records 
FROM
(
SELECT 69 ID,'snaha sadhu' student_name, 16 roll_number, '36-1' intake, 1 attendence_status, CONVERT(VARCHAR(10), getdate(), 103) date 
UNION ALL
SELECT 70 ID,'keya' student_name, 37 roll_number, '36-1' intake, 0 attendence_status, CONVERT(VARCHAR(10), getdate(), 103) date
union
SELECT 69 ID,'snaha sadhu' student_name, 16 roll_number, '36-1' intake, 1 attendence_status, CONVERT(VARCHAR(10), dateadd(day,1,getdate()), 103) date 
UNION ALL
SELECT 70 ID,'keya' student_name, 37 roll_number, '36-1' intake, 1 attendence_status, CONVERT(VARCHAR(10), dateadd(day,1,getdate()), 103)  date
)TAB

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + '[' + [date] + ']'
FROM (select distinct [date] from #attendance_records) s

SET @DynamicPivotQuery = 
  N'select *, cast(CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),' + replace(@ColumnName,',','+') + ')/CONVERT(DECIMAL(18,2),(select COUNT(distinct [date]) from #attendance_records)))*100 as varchar(10))+''%''
 from 
(SELECT student_name, roll_number, intake, ' + @ColumnName + '
 FROM #attendance_records       
        PIVOT(      sum(attendence_status)            
            FOR [date] IN (' + @ColumnName + ')) 
            AS P) x'

EXEC sp_executesql @DynamicPivotQuery