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

Ruchi Sharavat · Answer

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 Khabaria · Answer

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 Olatubosun · Answer

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 Khabaria · Answer

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?

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