Display dynamic columns in GridView using C# and SQL pivot function in ASP.Net

Hi sat,

Please refer below query and you need to create procedure and bind it to gridview. Call the BindGrid() method on Page_Load.

SQL

CREATE TABLE Pvt(Id INT,[Date] DATE,Gender VARCHAR(10),Participation INT)
INSERT INTO Pvt VALUES(1,'01/08/2016','Male',10020)
INSERT INTO Pvt VALUES(2,'01/08/2016','Female',8500)
INSERT INTO Pvt VALUES(3,'02/08/2016','Female',4490)
INSERT INTO Pvt VALUES(4,'02/08/2016','Male',4500)
INSERT INTO Pvt VALUES(5,'03/08/2016','Female',12510)
INSERT INTO Pvt VALUES(6,'04/08/2016','Male',11000)
INSERT INTO Pvt VALUES(7,'04/08/2016','Female',10510)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [Date],Gender,Participation FROM Pvt)
SELECT Gender,'+@ColumnName+' FROM CTE 
PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
ORDER BY Gender DESC'
EXEC(@DynamicPivotQuery)

Code

private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = @"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                    DECLARE @ColumnName AS NVARCHAR(MAX)
                    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]
                    SET @DynamicPivotQuery = ';	WITH CTE AS
							                    (
								                    SELECT [Date],Gender,Participation FROM Pvt
							                    )
							                    SELECT Gender,'+@ColumnName+' FROM CTE
							                    PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
							                    ORDER BY Gender DESC'
                    EXEC(@DynamicPivotQuery)";

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    gvDetails.DataSource = ds.Tables[0];
                    gvDetails.DataBind();
                }
            }
        }
    }
}

Output