Dynamic Columns in SQL Query

Jan-2010Clear

Jan-2010Rejected

Feb-20101Clear

Feb-2010 Rejected

Question

Hi all.. I need help in forming a SQL Query.

My requirement is - I have the table as follows-

ID	Date	Status	Count	Company	Location
1	1-1-2010	Clear	1	D	E
2	2-3-2010	Clear	2	C	F
3	1-1-2010	Rejected	3	B	G
4	2-3-2010	Rejected	4	A	H

Now, I want the output as follows in the sql query -

I am not able to create dynamic columns in query.

Please Help..

Thanks a lot 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 @Sumcolumnscsv varchar(MAX) declare @Sumcolumnscsvho 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) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + ']0) + isnull([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]0) + isnull([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'Sum([''') + Datename(mDate) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'ClearSum([' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(mDate) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Dateyear(Date) print 'isnull([' + @Sumcolumnscsvho + ']0) as Total' SET @sql = ' SELECT Company LocationStatussum([' + @Sumcolumnscsv +' isnull([' + @Sumcolumnscsvho + ']0) as Total 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 GROUP BY ROLLUP((Company LocationStatus' + '[' + @columnscsv + ']' + '))' --print @sql exec(@sql) hope this will help you.

Riddhi Valecha · Answer

Hi... Yes... That solution worked out ..... Thanks a lot........ Actually.... it was my mistake... I did a mistake in copying the logic.... Sorry for the trouble !!

Jignesh Trivedi · Answer

hi below link solution didn't work ? http://www.c-sharpcorner.com/Forums/Thread/241077/dynamic-columns-in-sql-query-urgent-plzz-help-me-out.aspx

Riddhi Valecha · Answer

Hi... Please help.... to get Column-wise total also.... Company Location April2013Clear April2013Rejected May2013Clear May2013Rejected TotalClear TotalRejected A H 1 2 1 2 1+1=2 2+2=4 B G 2 3 2 3 2+2=4 3+3=6 C F 3 4 3 4 3+3=6 4+4=8 6 9 6 9 6+4+2=12 4+6+8=18 PLZZ/.. Its Dam Urgent !!

Riddhi Valecha · Answer

Hi... Yaar....1 last query.... Please help.... I have a table as below - ID Date1 Date-2 Count Status 1 2013-1-1 2013-1-1 1 Clear 2 2013-1-1 2013-1-1 2 Rejected 3 2013-1-1 2013-3-3 3 Clear 4 2013-1-1 2013-1-1 4 Rejected Now I want the output as follows - Month TotalCount TotalClearCount TotalRejectedCount Clearin12days Clear after 12 days Efficiency Jan-2013 1+2+3+4=10 1+3 = 4 (Status = Clear) 2 + 4 = 6 (Status = Rejected) 1 (Date2-Date1 2+3+4 = 9 TotalCount/TotalClearCount * 100 i.e. 10/4*100 These are based on Date1 Column. Total count for Jan-2013 1+2+3+4 = 10 (Based on Date1) Clear Count = 1+3=4 and Rejected Count = 2+4=6 (Based on Status) Clear in 12 days => Date2- Date1 Clear after 12 days => In rest of the rows Date2-Date1 > 12. Last Column => Efficiency. I want to calculate all these values in stored procedure itself... Is it possible ?? Please help me out... Thanks a ton....

Jignesh Trivedi · Answer

ok you want add new row at last and that containing sum of all row (vertical). I thing CUBE and ROLLUP can help you... Ok to do this you have to change the which is dynamically create column for you. I think it is very difficult.

Riddhi Valecha · Answer

Hi... I already have sum as a new column Total. I am looking for a query to get below output - Company Location April2011Clear April2011Rejected May2011Clear May2011Rejected Total A H 1 8 9 6 1+8+9+6 = 48 B G 2 7 0 5 2+7+0+5= 14 C F 3 6 1 4 3+6+1+4= 14 D E 4 5 2 3 4+5+2+3=14 Total 1+2+3+4 = 10 8+7+6+5= 26 9+0+1+2=12 6+5+4+3=18 Also I have 1 more query... Suppose there are 2 dates columns say date-1 and date-2. Now I want to have the total days difference between 2 dates (which I can get by DateDiff() function in SQL). My query is - I want the output in the following format - Company April2011Clear ClearWithin12days ClearAfter12days Efficiency Total A 1 If (date2-date1) If (date2-date1)> 12 total = 1 else total =0 Clear in 12 days/total * 100 1 B 2 5 C 3 6 D 4 5 Please help.... Thanks a ton in advance....

Jignesh Trivedi · Answer

hi I think you are looking for the solution for horizontal sum right? so isunll(column10) + isunll(column20) + ... +isunll(columnm0) as totalH hope this will help you.

Riddhi Valecha · Answer

Hi... It worked out.... Thanks a ton once again...... The issue was in the data..... Hey...1 last query..... Any idea about how to get column-wise total ?? By executing this stored procedure I get row-wise total. I am trying to get column-wise total. Any idea how to set that logic ?? Please share.... Thanks a ton in advance ....

Jignesh Trivedi · Answer

it must work... can you please share some example data? so that I can check this with temp table...

Riddhi Valecha · Answer

Hi... I tried this query... The date column have dates in 2013-Sep Oct Nov and Dec Months. But this query is giving me only NovemberClear NovemberRejected DecemberClear and DecemberRejected Columns... I am not able to get Sep and OCt months... Any other solution ??

Jignesh Trivedi · Answer

hi try... declare @columnscsv varchar(MAX) declare @Sumcolumnscsv varchar(MAX) declare @Sumcolumnscsvho varchar(MAX) DECLARE @sql varchar(MAX) Select @columnscsv = COALESCE(@columnscsv + '][''') + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Clear][' + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Rejected' from #temp group by month(Date)year(Date) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + ']0) + isnull([''') + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Clear]0) + isnull([' + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Rejected' from #temp group by month(Date)year(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'Sum([''') + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Clear]) as ' + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'ClearSum([' +DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Rejected]) as ' + DateName(monthDateAdd(monthmonth(Date)  0)-1) + cast(year(Date) as varchar) + 'Rejected' from #temp group by month(Date)year(Date) query to remove duplicate moth year combination hope this will help you.

Riddhi Valecha · Answer

Hi.. No re.... The Solution DateName(month  DateAdd( month  month(Date)  0 ) - 1 ) is not working...... I am still working on this query..... And I am also trying to get totals on the Horizontal Side.. I require Totals on both the sides naa..... If you have any other solution... please help me out !! Thanks in advance once again...

Jignesh Trivedi · Answer

hi Riddhi I am doing just group by Date... so it is return duplicate value... I think... yes you can more do focus on below query to remove duplicate column.. 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) try to use DateName(month  DateAdd( month  month(Date)  0 ) - 1 ) instead of Datename(mDate) also add month(Date) in group by clause and remove date from group by... hope this will help you.

Riddhi Valecha · Answer

Sure... Sure..... 1 more query.... The Date Column values are not distinct. The values in the Date column are repeating. Because of this I am getting multiple time the @columnscsv in the pivot query. Any Solution to this also ?? If yes please share... Thanks a ton once again !!

Jignesh Trivedi · Answer

Please mark as answer if this post is help you.

Riddhi Valecha · Answer

Hi... Yes ... I got the totals on one-side... Thanks a ton...

Riddhi Valecha · Answer

Hi Jignesh... Thanks a ton yaar..... Your procedure solved half of my problem...... The only thing pending is - Horizontal and Vertical Totals .. I am trying out that logic only... Thanks a ton once again !!!

Jignesh Trivedi · Answer

ok Are you try my solution? also share logic how we can create no of column base on input parameter?

Riddhi Valecha · Answer

HI... No yaar.... That did not help me... I have to creat the number of columns in the SQL Server Stored Procedure. Not in the .cs file of the project. I will pass a numeric value(say 10) as a parameter to the stored procedure. And I want my sql query to generate that much of columns. Please help.... Thanks a ton ...

Jignesh Trivedi · Answer

hi Here get total is some what difficult... and also you required Date as column I think that generate wrong data. does your previous post help you? http://www.c-sharpcorner.com/Forums/Thread/240076/dynamic-columns-in-sql-query.aspx

Riddhi Valecha · Answer

Hi..... I once again need help here in the stored procedure.... I want 2 Total Colums - 1)Total Column name as ClearTotal. Here I need the Total count of all the clear months. i.e January2013Clear + march2013Clear 2) Total Colummn name as RejectedTotal. Here I need the total count of all rejecyed months. i.e. January2013Rejected + March2013Rejected. Please help me out.... Its dam urgent....... Please help !! Thanks a ton in advance.....