Dynamic Columns in SQL Query

Dynamic Columns in SQL Query

Answers (22)

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(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Date,year(Date) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Date,year(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear,Sum([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Date,year(Date) print 'isnull([' + @Sumcolumnscsvho + '],0) as Total' SET @sql = ' SELECT Company, Location,Status,sum([' + @Sumcolumnscsv +', isnull([' + @Sumcolumnscsvho + '],0) as Total FROM ( select Company,Location, Datename(m,Date) + cast(year(Date) as varchar) + Status as m,Count, Status from #temp ) as A PIVOT ( SUM(Count) FOR m IN (' + '[' + @columnscsv + ']' + ') )AS pivot1 GROUP BY ROLLUP((Company, Location,Status,' + '[' + @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(column1,0) + isunll(column2,0) + ... +isunll(columnm,0) 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(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear],[' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected' from #temp group by month(Date),year(Date) Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected' from #temp group by month(Date),year(Date) Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear]) as ' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear,Sum([' +DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected]) as ' + DateName(month,DateAdd(month,month(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(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' from #temp group by Date,year(Date) try to use DateName(month , DateAdd( month , month(Date) , 0 ) - 1 ) instead of Datename(m,Date) 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.....

Jan-2010Clear

Jan-2010Rejected

Feb-20101Clear

Feb-2010 Rejected