Hi everyone, This question has been asked multiple times, but I can't get any solution which could be employed in my scenario. I have a table with over 200 columns, of which 72 are named as MONTH_1, MONTH_2,....MONTH_72. In my query (which is written by joining 10+ tables), for each row, I need to display one of these columns dynamically based on difference between two dates in terms of month. Is there any way to write a function which will execute the exec() or sp_executesql() and give me the required data? Let me know as soon as possible. Any helpful suggestion is welcomed.
You could try something like this: select sum(MONTH_1) as MONTH_1 , sum(MONTH_2) as MONTH_2 from ( select case when datediff(month, start_date, end_date) = 1 then revenue else 0.00 end as MONTH_1 , case when datediff(month, start_date, end_date) = 2 then revenue else 0.00 end as MONTH_2 from table_name ) x
is every row allowed to return a different column based on that rows distance in time from @parameter_date? If so, you could potentially return all 72 columns. If every row in the results will return the same month column then your concatenation approach will work. for example: declare @sql varchar(500) = 'select ' + @var + ' from mytable where etc'; exec(@sql); If the column is dynamic enough that it needs to change on a row-by-row basis then you can either run a query ahead of time to see which columns you need to include then generate the query to select those columns (returning each dynamically selected column in its own reserved position - somewhere between 0 and 72 columns) or you can use a case in the select statement to grab data from the appropriate month column and place that value into a shared column. for example, using days instead of months: declare @parameter_date datetime = getdate(); create table #blah (first_bill_date datetime, characters varchar(10), c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int) insert #blah values (getdate()-1, 'we', 1, null, null, null, null, null, null, null), (getdate()-2, 'are', null, 2, null, null, null, null, null, null), (getdate()-3, 'working', null, null, 3, null, null, null, null, null), (getdate()-4, 'around', null, null, null, 4, null, null, null, null), (getdate()-5, 'a', null, null, null, null, 5, null, null, null), (getdate()-6, 'broken', null, null, null, null, null, 6, null, null), (getdate()-7, 'data', null, null, null, null, null, null, 7, null), (getdate()-8, 'model', null, null, null, null, null, null, null, 8) select first_bill_date, characters, case convert(varchar(3),datediff(day,first_bill_date,@parameter_date)) when 1 then c1 when 2 then c2 when 3 then c3 when 4 then c4 when 5 then c5 when 6 then c6 when 7 then c7 when 8 then c8 end as special_column from #blah order by first_bill_date desc drop table #blah If the contatenation approach works because every query will only return values from a single month column then congratulations - you win the easy prize! If different rows can return differnt months and it's okay to return them using a "special_column" type name then it won't matter that the case statement is too cumbersome; the choice was made 6 years ago (and it's really not *that* cumbersome to paste in and edit 72 "when NUMBER then column_NUMBER" type entries) If you end up needing somewhere between 0 and 72 month columns in the result set then you get to code up a bunch of query generating statements and execute those dynamically.