Dynamic Columns in SQL Query

Question

Hi all...

I need help in creating dynamic columns in SQL Query.

My requirement is -
The user have to select 2 dates from the front-end.
Let the selected dates be - Jan-2010 to Dec-2013.

The table in the database that I have is-

ID	Company	Location	Date	Count	Status
1	TCS	Mumbai	1-1-2010	1	Clear
2	Zensar	Hydrabad	2-2-2010	2	Rejected
3	Infosys	Pune	3-3-2011	4	Clear
4	IBM	Pune	4-4-2011	3	Clear
5	Microsoft	Singapore	5-5-2012	5	Rejected
6	Google	US	6-6-2013	4	Rejected

Total Months (Jan-2010 to Dec-2013 =48 Months) Columns.

Total Status = 2 (Clear and Rejected).

Hence, total dynamic columns = 48 * 2 = 96

Name of Columns - Jan2010Clear, Jan2010Rejected, Feb2010Clear, Feb2010Rejected, etc and so on.

The output I want is is follows -

Company	Location	Jan2010Clear	Jan2010Rejected	Feb2010Clear	Feb2010Rejected	Mar2011Clear	March2011Rejected	Total
		1	0	0	2	4	0	1+2+4=7



	Total	1	0	0	2	4	0	1+2+4=7

I am unable to get the result using pivot query.

Please help me out...

Thanks a ton to all.. in advance...

Jignesh Trivedi · Answer

hi Try Create table #temp (ID int Company varchar(25) Location varchar(25) Date date Count int Status varchar(25)) insert into #temp values(1'TCS''Mumbai''1-1-2010'1'Clear') (2'Zensar''Hydrabad''2-2-2010'2'Rejected') (3'Infosys''Pune''3-3-2011'4'Clear') (4'IBM''Pune''4-4-2011'3'Clear') (5'Microsoft''Singapore''5-5-2012'5'Rejected') (6'Google''US''6-6-2013'4'Rejected') declare @columnscsv varchar(MAX) DECLARE @sql varchar(MAX) Select @columnscsv = COALESCE(@columnscsv + '][''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear][' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) print '[' + @columnscsv + ']' SET @sql = ' SELECT * FROM ( select CompanyLocation Datename(mDate) + cast(year(Date) as varchar) + Status as mCount Status from #temp ) as A PIVOT ( SUM(Count) FOR m IN (' + '[' + @columnscsv + ']' + ') )AS pivot1' exec(@sql) not sure it is work for you or not...

Manikavelu Velayutham · Answer

As per your requirements number of columns will differ based on the User selection of the dates. So its difficult to use Pivot Query or through sql query. First You have to get the result Group By Company Location & Date. Next you need to loop through the above result and transpose it as per your requirment. Something like below private DataTable GetTransposedTable( DataTable dt) { DataTable newTable = new DataTable (); newTable.Columns.Add( new DataColumn ( 0  typeof ( string ))); for ( int i = 0; i { DataRow newRow = newTable.NewRow(); newRow[0] = dt.Columns[i].ColumnName; for ( int j = 1; j { if (newTable.Columns.Count newTable.Columns.Add( new DataColumn (j.ToString() typeof ( string ))); newRow[j] = dt.Rows[j - 1][i]; } newTable.Rows.Add(newRow); } return newTable; } Thats the only way to acheive your requirement.