How to Display student attendance in gridview using SQL and Gridview

I am using SQL Server 2012 and VS 2012 C# I have designed database table Roll_No, Attend_Date, Attend_Status while storing data i store like this

______________________________________
Roll_No Attend_Date Attend_Status
---------------------------------------
1   04-01-14    1
2   04-01-14    1
3   04-01-14    1
1   05-01-14    0
2   05-01-14    1
3   05-01-14    0
1   06-01-14    1
2   06-01-14    1
3   06-01-14    1
----------------------------------------

But I have to show in grid-view like this

__________________________________________________________________
Roll_No 04-01-14    05-01-14    06-01-14    07-01-14
-------------------------------------------------------------------
1   1              0              1             1
2   1              1              1             1
3   1              0              1             1
-------------------------------------------------------------------

Can you Help me please… How can I achieve this result..

Answer

You can do this by listing each date as column name while doing PIVOT or use dynamic SQL to generate column list and then apply PIVOT.

PIVOT is used to change row data to columns.

create table #attendance (rollno int,attend_date varchar(30),attend_status int)
insert into #attendance values(1,'04-01-14',1)
insert into #attendance values(2,'04-01-14',1)
insert into #attendance values(3,'04-01-14',0)
insert into #attendance values(1,'05-01-14',1)
insert into #attendance values(2,'05-01-14',0)
insert into #attendance values(3,'05-01-14',1)
insert into #attendance values(1,'06-01-14',1)
insert into #attendance values(2,'06-01-14',1)
insert into #attendance values(3,'06-01-14',0)

Declare @colList varchar(max)
Declare @qry varchar(max)

SET @colList = STUFF((SELECT distinct ',' + QUOTENAME(c.Attend_Date) 
            FROM #attendance c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @qry = 'SELECT rollno,'+@colList+'
FROM (
    SELECT Rollno,attend_date,attend_status

    FROM #attendance
) as s
PIVOT
(
    MAX(Attend_Status)
    FOR Attend_Date IN (' + @colList + ')
) pvt '

EXEC(@qry)

drop table #attendance

OUTPUT

Rollno  04-01-14  05-01-14  06-01-14
1             1      1       1
2             1      0       1
3             0      1       0

Now you can bind this resultset to your GridView