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