Split Comma Separated rows into multiple rows in ASP.Net using C#

Hi RichardSa,

Refer below code.

SQL

CREATE TABLE RECEIPTS (RNumber INT,Item VARCHAR(100),Qty VARCHAR(100),Price VARCHAR(100),Amount VARCHAR(100),CSymbol VARCHAR(100))
INSERT INTO RECEIPTS VALUES(1,'sweets,bread','2,3','5,20','10,60','$')
INSERT INTO RECEIPTS VALUES(1,'cake,noodle','3,5','10,40','30,200','£')

HTML

<asp:GridView runat="server" ID="Gridview1" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Item" HeaderText="Item" />
        <asp:BoundField DataField="Qty" HeaderText="Quantity" />
        <asp:BoundField DataField="Rate" HeaderText="Rate" />
        <asp:BoundField DataField="Amount" HeaderText="Amount" />
    </Columns>
</asp:GridView>

Code

protected void Page_Load(object sender, EventArgs e)
{
    GetValueData();
}

private void GetValueData()
{
    try
    {
        if (Session["Id"] != null)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Item");
            dt.Columns.Add("Qty");
            dt.Columns.Add("Rate");
            dt.Columns.Add("Amount");
            dt.Columns.Add("CSymbol");
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                using (SqlCommand cmd = new SqlCommand("SELECT Item,Qty,Rate,Amount,CSymbol FROM RECEIPTS WHERE (RNumber = '" + Session["Id"] + "')", con))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.CommandType = CommandType.Text;
                        sda.SelectCommand = cmd;
                        sda.Fill(dt);

                        DataTable dt1 = dt.Clone();
                        foreach (DataRow dr in dt.Rows)
                        {
                            for (int i = 0; i < dr["item"].ToString().Split(',').Length; i++)
                            {
                                if (!string.IsNullOrEmpty(dr["item"].ToString().Split(',')[i]))
                                {
                                    DataRow dr1 = dt1.NewRow();
                                    dr1["Item"] = dr["Item"].ToString().Split(',')[i].Trim();
                                    dr1["Qty"] = dr["Qty"].ToString().Split(',')[i].Trim();
                                    dr1["Rate"] = string.Format("{0} {1}", dr["CSymbol"].ToString(), (Convert.ToDecimal(dr["Rate"].ToString().Split(',')[i].Trim()).ToString("N", new CultureInfo("en-US"))));
                                    dr1["Amount"] = string.Format("{0} {1}", dr["CSymbol"].ToString(), (Convert.ToDecimal(dr["Amount"].ToString().Split(',')[i].Trim()).ToString("N", new CultureInfo("en-US"))));
                                    dt1.Rows.Add(dr1.ItemArray);
                                }
                            }
                        }
                        Gridview1.DataSource = dt1;
                        Gridview1.DataBind();
                    }
                }
            }
        }
    }
    catch (SqlException ex)
    {
        string msg = "Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
}

Screenshot