Create dynamic table based on the result of a query

Question

Dear Code Master,

 

I hope this question meet you all in good health.

Please i am trying to create a table that will display price trend base on the result of a query from price table the following table

 

 

Description	Unit	Rate	Mnths	Yrs	States
Velox beam unit size 250mm x 140mm x 115mm high	Nr	12.00	FEB	2014	Abuja
Masonia	Length	13.00	FEB	2014	Abuja
533 x 210mm x 83kg/m	Length	34.20	FEB	2014	Abuja
32mm x 4mm thick	Length	1,190.90	FEB	2015	Abuja
20.0mm Thick	Sheet	2,100.10	FEB	2015	Abuja
600mm x 600mm	Nr	1,200.10	FEB	2015	Abuja
Gloss (white)	Gal	1,200.00	FEB	2016	Abuja
Deep-well submersible pump 147 meters suction depth, 7.5kw collection output	Nr	2,000.00	FEB	2016	Abuja
6.0mm2 Cable	Coil	2,200.00	FEB	2016	Abuja
5 Amp two gang, ditto	Nr	3,200.00	FEB	2017	Abuja
FM 200 discharge nozzle	Nr	3,200.00	FEB	2017	Abuja
Clear glass block size 145mm 145mm 95mm	Nr	1,000.00	FEB	2017	Abuja
Ribbed glass block size 145mm 145mm 95mm	Nr	9,821.00	FEB	2017	Abuja
28 swg (Effective 18m2/Bundle)	Bundle	10,234.00	FEB	2017	Abuja

 

I want the table to become as follows

 

Description	2014	2015	2016	2017
533 x 210mm x 83kg/m	12.00	12.10	12.11	12.12
Masonia	31.00	22.00	33.00	21.00
533 x 210mm x 83kg/m	22.00	21.00	233.00	33.00

 

 My tried code is below

if (DrpMonth.Text != "" && DrpYear.Text != "" && DrpYear1.Text != "")
            {
                var DT = new DataTable();
                DT = ConnectAll.GetDataTable(string.Concat("select *  from tbl_SubBasicPrice where Category ='" + DropDownList4.SelectedItem.Text.Trim() + "' ",
                              " and subCategory='" + DropDownList5.SelectedItem.Text.Trim() + "' and mnths='" + DrpMonth.SelectedItem.Text.Trim() + "' ",
                              " and yrs BETWEEN '" + DrpYear.SelectedItem.Text.Trim() + "' and '" + DrpYear1.SelectedItem.Text.Trim() + "' ",
                              " and states= '" + DropDownList3.SelectedItem.Text.Trim() + "'"));

                using (SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe()))
                {
                    cn.Open();

                    List<string> Col = new List<string>();
                    List<string> Ro = new List<string>();

                    foreach (DataRow r in DT.Rows)
                    {
                        foreach (DataColumn c in DT.Columns)
                        {
                            //Col.Add(c.ColumnName.ToString());
                            if (c.ColumnName.Trim() == "Yrs")
                            {
                                Ro.Add(r["Yrs"].ToString());
                            }

                        }
                    }
                    string SQL = "if object_id(N'Temp_SubBasicPrice',N'U') is not null ";
                    SQL += " begin ";
                    SQL += "    DROP table Temp_SubBasicPrice ";
                    SQL += " end ";
                    SQL += " else ";
                    SQL += " begin ";
                    SQL += " DECLARE @SQL1 NVARCHAR (4000) ";
                    SQL += " SELECT @SQL1 ='CREATE TABLE Temp_SubBasicPrice( ";
                    SQL += "    [ID] int identity(1,1) not null,";
                    SQL += "    Description nvarchar(max), ";

                    foreach (string c in Ro)
                    {
                        SQL += " " + c.ToString() + "  numeric(8,2), ";
                    }
                    SQL.Replace(",", "");
                    SQL += "  )' ";
                    SQL += " exec (@SQL1)";
                    SQL += "  end ";

                    SqlCommand cmdA = new SqlCommand(SQL, cn);
                    cmdA.ExecuteNonQuery();
                    cmdA.Dispose();
                    cn.Close();
                }

            }

 the code is working but not creating the table.

 

Any solution will be apreciated.

 

Thank you and God bless you all