question

Rinky avatar image
Rinky asked

Dynamic query - Column name from string

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.
functionsdynamic-sqlexecsp_executesql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HI David, Thanks for replying. But as I mentioned it is dynamic and there are 72 such columns, if I go on to write the query with case statement, it would involve writing 72 such cases which is too cumbersome. My primary strategy revolved around using datediff function, and then using concat to get the column name (eg - @var='MONTH_'+convert(varchar(3),datediff(month,first_bill_date,@paramter_date)) which would give me, month_5) and then I wanted to write the SQL-String - "select +@var+ from mytable where etc" I have the string which would need to be executed. I just can't find any way to write a function to execute this SQL-String
0 Likes 0 ·
Gazz avatar image
Gazz answered
You should normalise you table. You probably shouldn't have 72 columns. If you fix this then your problem will probably also be fixed
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The client wants 6 years of data in a single table. So it has the columns MONTH_1..12 for the first year, next 12 for next year and so on. I am not in the position to change the structure, I just need to get the result in a faster way.
0 Likes 0 ·
KenJ avatar image
KenJ answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ken, I am with you in the initial approach you mentioned, i.e. using exec(@sql). I have been trying to create a function which would accept the usage of that function, but MS-SQL does not allow using that (exec()) or sp_executesql(sql-string) as part of a function (If you have any workaround on that one, I would be really grateful for your help) Now re the other approach (my-broken-data-model one) I think that writing case statements from 1..72 is cumbersome because my current way is to use the pivot-unpivot in a function. On pivoting the column headers (MONTH_1..MONTH_72), I can just compare the column name to the name of my required column name (through concat) and return the value in that column. This method works fine, but it takes about a minute or so to execute, which is why I am trying to improve the efficiency. Thanks for your reply. Please let me know if there is a way to execute the Sql-String in function.
0 Likes 0 ·
If you look at the "Limitations and Restrictions" section of the User Defined Fuctions topic in MSDN, you'll see that *User-defined functions cannot make use of dynamic SQL or temp tables*. Table variables are allowed https://msdn.microsoft.com/en-us/library/ms191320.aspx If the output works for you, I would recommend at least trying the case statement approach in a development scenario. It may seem cumbersome to develop, but you're seeing it live how the pivot/unpivot approach is cumbersome at runtime. If the case statement runs in a few seconds rather than a minute, it will pay for the 5 extra minutes of development time before it has even been executed 10 times.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.