Create dynamic table based on the result of a query

Abraham Olatubosun

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ruchi Sharavat8y ago
I believe that the problem that is causing issue for table creation is the name of dynamic columns. Actually name of these columns are numeric i.e. 2014,2015 etc and SQL does not allow to add numbers as column names the way we use for others.

You will have to use double quotes ("2014") like this with the column names so that while debugging your string SQL should look like below:
if object_id(N'Temp_SubBasicPrice',N'U') is not null
begin    DROP table Temp_SubBasicPrice  end
  else
begin
DECLARE @SQL1 NVARCHAR (4000)
   SELECT @SQL1 ='CREATE TABLE Temp_SubBasicPrice(  [ID] int identity(1,1) not null,     Description nvarchar(max), "2014" numeric(8,2), "2015" numeric(8,2)) ';
   EXEC(@SQL1);
END
If this was not the problem in your case than please show the value of string SQL you have during debugging to check the issue.
Chirag Khabaria8y ago
Here is the Query..
Declare @Original Table
(
    Descript varchar(10),
    Price int,
    Yrs int
)

Insert into @Original
Select 'Rice', 200, 2001 Union All
Select 'Rice', 210, 2002 Union All
Select 'Beans', 300, 2003

Select * From @Original

-- Query for Transporing Data
Select * From
(
    Select Descript, Isnull(Price,'') Price, Yrs
    From @Original
)  F
Pivot
(
    Max(Price) For Yrs In ( [2001],
                            [2002],
                            [2003]
                         )
) as P

Output

Descript   2001        2002        2003
---------- ----------- ----------- -----------
Beans      NULL        NULL        300
Rice       200         210         NULL

Abraham Olatubosun8y ago

Many thanks Chirag, you are correct i want to transpose just the year record i.e Yrs Field while creating the table so it will look like the one below:

Original

Description	Price	Yrs
Rice	200	2001
Rice	210	2002
Beans	300	2003

Result

Description	2001	2002	2003
Rice	200	210
Beans			300

the table above discribe how i tried to create table.

Thank you

Chirag Khabaria8y ago
your output doesnt seems to be matching with your data. Can you have them correctly synced up?

Are you trying Transpose query i.e. rows to column?