Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snagar
Starting Member
14 Posts |
 Posted - 2012-07-07 : 10:52:05
|
| Hi,I have a table with 12 columns like col1,col2,col3,col4,col5.....col12Now i have to write a query which returns these columns based on current monthfor example if current month is 1 then only col1 should returnwhen month is 2 then col1 and col2 will be returned and so on.One approach is to use if else condition based on month but for this i have to write 12 select statements in stored proc.second approach i am trying is below sql query-Declare @curmonth int = Month(Getdate())Declare @sqlcols varchar(max)set @sqlcols = (Select Case when @curmonth=1 Then 'Col1' End when @curmonth=2 Then 'Col1,Col2' End)Now i have desired col names in @sqlcols variable.Now my idea is to use split function and use these col names in select statement but i am not able to write sql query for this.Please help either for this approach or if there is another better way to get desired output.Thanks! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-07-07 : 11:49:57
|
Sounds like a bad database design.Why do you have to retrieve that columns based on month?Can you do that in your front end?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
snagar
Starting Member
14 Posts |
Posted - 2012-07-07 : 12:13:56
|
quote:
Originally posted by webfred
Sounds like a bad database design.Why do you have to retrieve that columns based on month?Can you do that in your front end?
No, you're never too old to Yak'n'Roll if you're too young to die.
No , i need to do it on database side only.In my table i have cols for each month like 1st col have some data for jan month and so on.Now if you have current month value 4 then the output must contain cols till 4th month i.e four columns. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-07 : 12:41:29
|
| If I understood your requirement correctly, if you were to call the stored procedure in January, the record set returned will have only one column. In February, it will have 2 columns, in March 3 columns and so on.Intuitively, it seems like there is something that is not robust or sound about that requirement and the design. For one thing, there can be ambiguity about the time because of differences in system clocks or time zones(if the client and server are in different geographic regions), daylight savings time, UTC vs local time etc. That may not be an issue for you, but something to consider, nonetheless. But, if your client application REQUIRES that you provide the data in this manner, then you have to do what you have to do.If you do end up needing to do this, in spite of having to write the 12 select statements, my preference would be to use the first approach you thought of. Keeps it simple and sweet. Format it nicely and put couple of comments in there so it is obvious to anyone who looks at it what you are trying to do.Alternatively, you can construct a dynamic SQL query string and execute it - but that sort of obfuscates the logic. But if you still want to do it AMA and have difficulty with it, post a reply. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
snagar
Starting Member
14 Posts |
|
|
|
|
|
|
|
Retrive Specific Columns based on Month no.