my aspx page code
<asp:GridView ID="gvStaff" runat="server" EmptyDataText ="No Record found" CssClass="table table-striped table-hover" BorderStyle="None" GridLines="Vertical" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblStName" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
code Behind
Private Sub bindinboxgridview()
Dim amonth As Integer = CInt(ddlMonth.SelectedValue.ToString)
Dim ayear As Integer = CInt(ddlYear.SelectedItem.Text.ToString)
Dim constring As String = ConfigurationManager.ConnectionStrings("EIMSConnectionString").ConnectionString
Dim con As SqlConnection = New SqlConnection(constring)
Try
con.Open()
Dim sqlComm As SqlCommand = New SqlCommand("sp_show_staff_att", con)
sqlComm.Parameters.AddWithValue("@attmonth", amonth)
sqlComm.Parameters.AddWithValue("@attyear", ayear)
sqlComm.Parameters.AddWithValue("@result", SqlDbType.Int)
sqlComm.CommandType = CommandType.StoredProcedure
Dim ds As DataSet = New DataSet("TimeRanges")
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = sqlComm
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
gvStaff.DataSource = ds.Tables(0)
gvStaff.DataBind()
Else
lblerror.ForeColor = Drawing.Color.Red
lblerror.Text = "Record Not Found"
End If
Catch ex As Exception
lblerror.ForeColor = Drawing.Color.Red
lblerror.Text = ex.Message.ToString
Finally
con.Close()
End Try
End Sub
my stored procedure is
ALTER proc [dbo].[sp_show_staff_att](@attmonth int ,@attyear int,@result int output)
as
BEGIN
SELECT *
INTO #Temp
FROM
(SELECT [Emp_Code] ,[Name],[Status],attDay
FROM Table_Attendance_Staff WHERE attMonth =@attmonth and attYear=@attyear ) as x
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),@ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(attDay) FROM (SELECT DISTINCT attDay
FROM #Temp) AS [Days]
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [attDay],name,Status FROM #Temp )
SELECT name,'+@ColumnName+' FROM CTE
PIVOT (MAX(Status) FOR [attDay] IN('+@ColumnName+')) p
ORDER BY name DESC'
EXEC(@DynamicPivotQuery)
drop table #Temp
END
my table as
CREATE TABLE [dbo].[Table_Attendance_Staff](
[Emp_Code] [varchar](30) NOT NULL,
[Name] [varchar](60) NOT NULL,
[Status] [char](1) NULL,
[AttendanceDate] [varchar](50) NOT NULL,
[attDay] [int] NULL,
[attMonth] [int] NULL,
[attYear] [int] NULL