code

protected void Showgrid()
    {
        try
        {
            string str = ddlfinancialyear.SelectedItem.Text.Trim();
            string[] token = str.Split('-');
            string[] tokens = str.Split('-');
            string first = token[token.Length - 2];
            string last = tokens[tokens.Length - 1];

            string sSql = "SELECT ";
            sSql += "[PurchaseType] as 'PurchaseType',  ";
            sSql += "[ProductType] as 'ProductType',  ";
            sSql += "[01] AS Jan, ";
            sSql += "[02] AS Feb, ";
            sSql += "[03] AS Mar, ";
            sSql += "[04] AS Apr, ";
            sSql += "[05] AS May, ";
            sSql += "[06] AS Jun, ";
            sSql += "[07] AS Jul, ";
            sSql += "[08] AS Aug, ";
            sSql += "[09] AS Sep, ";
            sSql += "[10] AS Oct, ";
            sSql += "[11] AS Nov, ";
            sSql += "[12] AS Dec ";


            sSql += "FROM ";
            sSql += "( ";
            sSql += "SELECT [PurchaseType],[ProductType], DATEPART(Month, [Date]) AS Month, CONVERT(decimal(10,2), CONVERT(varbinary(20), [PriceConsume]))as PriceConsume,[Active] ";
            sSql += "FROM [HB_CloseStock]";
            sSql += "Where ";
            if (ddlfinancialyear.SelectedValue != "")
            {
                sSql += "CONVERT (datetime, Date, 103) >=  CONVERT (datetime, '01/04/" + first + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '31/03/" + last + "', 103)";
            }
            else
            {
                sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '" + DateTime.Now.ToString("01/04/yyyy") + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '" + DateTime.Now.ToString("31/03/yyyy") + "', 103)";
            }
            sSql += ")";
            sSql += "N ";
            sSql += "PIVOT ";
            sSql += "( ";
            sSql += "SUM ([PriceConsume]) ";
            sSql += "FOR Month IN ";
            sSql += "(  ";
            sSql += "[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ";
            sSql += ") AS pvt ";
            sSql += "WHERE [Active] = 'Y' ";
            if ((ddlPurchaseType.SelectedItem.Text != "-- Select --") && (ddlProductType.SelectedItem.Text != "-- Select --"))
            {
                sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "' and  [ProductType]='" + ddlProductType.SelectedItem + "'";
            }
            else if (ddlPurchaseType.SelectedItem.Text != "-- Select --")
            {
                sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "'";
            }
            sSql += "ORDER BY [ProductType] ";
            SqlDataSource1.SelectCommand = sSql;
            dbgrid.DataBind();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

crosstab

protected void Showgrid()
    {
        try
        {
            string str = ddlfinancialyear.SelectedItem.Text.Trim();
            string[] token = str.Split('-');
            string[] tokens = str.Split('-');
            string first = token[token.Length - 2];
            string last = tokens[tokens.Length - 1];

            string sSql = "SELECT ";
            sSql += "[PurchaseType] as 'PurchaseType',  ";
            sSql += "[ProductType] as 'ProductType',  ";
            sSql += "[01] AS Jan, ";
            sSql += "[02] AS Feb, ";
            sSql += "[03] AS Mar, ";
            sSql += "[04] AS Apr, ";
            sSql += "[05] AS May, ";
            sSql += "[06] AS Jun, ";
            sSql += "[07] AS Jul, ";
            sSql += "[08] AS Aug, ";
            sSql += "[09] AS Sep, ";
            sSql += "[10] AS Oct, ";
            sSql += "[11] AS Nov, ";
            sSql += "[12] AS Dec ";


            sSql += "FROM ";
            sSql += "( ";
            sSql += "SELECT [PurchaseType],[ProductType], DATEPART(Month, [Date]) AS Month, CONVERT(decimal(10,2), CONVERT(varbinary(20), [PriceConsume]))as PriceConsume,[Active] ";
            sSql += "FROM [HB_CloseStock]";
            sSql += "Where ";
            if (ddlfinancialyear.SelectedValue != "")
            {
                sSql += "CONVERT (datetime, Date, 103) >=  CONVERT (datetime, '01/04/" + first + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '31/03/" + last + "', 103)";
            }
            else
            {
                sSql += "CONVERT (datetime, Date, 103) >= CONVERT (datetime, '" + DateTime.Now.ToString("01/04/yyyy") + "', 103) and CONVERT (datetime, Date, 103) <= CONVERT (datetime, '" + DateTime.Now.ToString("31/03/yyyy") + "', 103)";
            }
            sSql += ")";
            sSql += "N ";
            sSql += "PIVOT ";
            sSql += "( ";
            sSql += "SUM ([PriceConsume]) ";
            sSql += "FOR Month IN ";
            sSql += "(  ";
            sSql += "[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ";
            sSql += ") AS pvt ";
            sSql += "WHERE [Active] = 'Y' ";
            if ((ddlPurchaseType.SelectedItem.Text != "-- Select --") && (ddlProductType.SelectedItem.Text != "-- Select --"))
            {
                sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "' and  [ProductType]='" + ddlProductType.SelectedItem + "'";
            }
            else if (ddlPurchaseType.SelectedItem.Text != "-- Select --")
            {
                sSql += "and [PurchaseType] = '" + ddlPurchaseType.SelectedItem + "'";
            }
            sSql += "ORDER BY [ProductType] ";
            SqlDataSource1.SelectCommand = sSql;
            dbgrid.DataBind();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }