Preface

How bad it would be to start working as a frog boiled in warm water. So we should keep a calm mind, think about what we should and shouldn't do, improve our abilities and add something to ourselves. Well, even if it's really cooked, it'll be delicious.

Background of the problem

This problem was encountered in the previous work and was finally solved after some efforts. Here we summarize the problems and solutions.

Problem Description

There are two tables in sql server database, main table and detail table. Ultimately, tables need to be displayed on the front page, which requires that the data of detail table be attached to the corresponding row as a dynamic column based on the main table.

Here we give the table for the experiment and what we need to show in the end:

Main table table1:

Detailed table 2:

Styles to display:

Solutions

The first step is to convert the data into the following format:

The second step is to use C # code to process the data and make it into the final required format.

Specific realization

select a.*,
    stuff((select ',' + t.task from table2 as t where t.name=a.name for xml path('')),
        1,1,'') as task
from table1 as a

That's the first step.

The front-end code first gives:

    <form id="form1" runat="server">
        <div>
            <p>This is the Table:</p>
           <table border="1">
               <asp:Label runat ="server" ID="lblTable"></asp:Label>
           </table>
        </div>
    </form>

Now we just need to splice the table's html code in the background and assign it to lblTable.

Background, we need to divide the task column into multiple columns, and the number of columns is variable with the change of task content.

    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "server=.;uid=sa;pwd=My own password;database=My own database";
        cn.Open();
        string sqlstr = @"
                            select a.*,
                                stuff((select ',' + t.task from table2 as t where t.name = a.name for xml path('')),
                                    1,1,'') as task
                            from table1 as a

                         ";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandText = sqlstr;
        SqlDataReader reader = cmd.ExecuteReader();

        DataTable dt = ReaderToTable(reader);
        dt = DealTable(dt);
        string htmlStr = GetHtmlStrByDataTable(dt);
        lblTable.Text = htmlStr;

    }
    protected DataTable ReaderToTable(SqlDataReader dr)
    {
        DataTable dt = new DataTable();

        for (int i = 0; i < dr.FieldCount; i++)
        {
            dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
        }

        object[] objValues = new object[dr.FieldCount];
        while (dr.Read())
        {
            dr.GetValues(objValues);
            dt.LoadDataRow(objValues, true);
        }
        dr.Close();

        return dt;
    }

    protected DataTable DealTable(DataTable dt)
    {
        //Get task column data and store it in list
        List<string[]> list = new List<string[]>();
        for(int i=0;i<dt.Rows.Count;i++)
        {
            string[] strs = dt.Rows[i]["task"].ToString().Split(',');
            list.Add(strs);
        }
        //Get the longest length in strs
        int maxlen = 0;
        foreach(string[] strs in list)
        {
            if (strs.Length>maxlen)
            {
                maxlen = strs.Length;
            }
        }
        //Delete task column
        dt.Columns.Remove("task");
        //Add maxlen columns
        for(int i=0;i<maxlen;i++)
        {
            dt.Columns.Add("task" + (i + 1).ToString());
        }
        //Assign data to newly added columns
        int newColStart = dt.Columns.IndexOf(dt.Columns["name"])+1;
        for (int i=0;i<dt.Rows.Count;i++)
        {
            for(int j=newColStart;j<newColStart+maxlen;j++)
            {
                int newColNo = j - newColStart;
                if (list[i].Length>newColNo)
                {
                    dt.Rows[i][j] = list[i][newColNo];
                }
            }
        }

        return dt;
    }

    protected string GetHtmlStrByDataTable(DataTable dt)
    {
        StringBuilder sb = new StringBuilder();

        //Header
        sb.Append("<tr>");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
        }
        sb.Append("</tr>");

        //data
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            sb.Append("<tr>");
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                sb.Append("<td>" + dt.Rows[i][j].ToString() + "</td>");
            }
            sb.Append("</tr>");
        }

        return sb.ToString();
    }

In this way, the ultimate goal has been achieved: