asked
how to do a search based on the column name ?
suppose i have a table which contains name jan_data feb_data March_data Apr_data May_data June_data july_data aug_data sept_data oct_data nov_data dec_data Data_year mike 12 45 56 77 2011 jazyb 24 34 67 67 87 90 55 88 33 12 26 76 2010 ramu 12 99 22 45 88 34 56 56 67 79 99 12 2009 rocky 12 09 76 33 2011 cole 24 34 67 67 87 90 55 88 33 12 26 76 2010 briju 12 99 22 45 88 34 56 56 67 79 99 12 2009 and long list follows this .... Now i want to display the records belonging to current month and year only for example in my example i want to extract the entries for april 2011 so that is mike 77 rocky 33
How about this: declare @sql nvarchar(2000); set @sql = 'select name, ' + LEFT(DATENAME(m, GETDATE()), 3) + '_data from your_table where data_year = YEAR(GETDATE())'; exec sp_executesql @sql; (replace your_table with the name of your table) *Note: it assumes that your data columns are named using the first three letters of the month name followed by "_data". In your example you seem to be mixing this naming convention with another one (some of your columns use the full month name, for instance March_data). Hopefully this is an error or they can be renamed?*
SELECT Apr_data FROM table_name WHERE date_year = '2011'
select name , apr_data from table where data_year = YEAR(GETDATE())
@Kannan and @TRAD - I have a feeling that @AbhiD wants a more dynamic solution than just the select. Something like this should get you started: CREATE TABLE dbo.Test (Name varchar(255), AprColumn int, MayColumn int, data_year int) DECLARE @sql varchar(MAX), @TableName varchar(255) SET @TableName = 'dbo.Test' SELECT @sql='SELECT '+ 'Name,' +STUFF((SELECT ',' + name FROM sys.columns C WHERE object_id = OBJECT_ID(@TableName) AND name LIKE LEFT(DATENAME(mm, GETDATE()), 3) + '%' FOR XML PATH('')), 1, 1, '') +' FROM '+@TableName + ' WHERE data_year = '+DATENAME(YEAR,GETDATE()) SELECT @sql DROP TABLE dbo.Test This builds up a dynamic query, finding the column that has starts with the first three letters of the current month (currently April so 'Apr'). Run the query to see how it works. The final select can be used to do whay you are wanting. This is a rough mock-up of what you may want to do.
thx williamD ... it gave me a string output like "SELECT Name,Apr_Factor FROM dbo.TR_Factor_History WHERE data_year = 2011" ... it can be modified for my result as follows DECLARE @sql nvarchar(2000), @TableName varchar(255) SET @TableName = 'dbo.TR_Factor_History' Set @sql='SELECT '+ 'instrument_id,asset_type,Asset_Sub_Type,Security_Description,' +STUFF((SELECT ',' + name FROM sys.columns C WHERE object_id = OBJECT_ID(@TableName) AND name LIKE LEFT(DATENAME(mm, GETDATE()), 3) + '%' FOR XML PATH('')), 1, 1, '') +' FROM '+@TableName + ' WHERE factor_year = '+DATENAME(YEAR,GETDATE()) exec sp_executesql @sql
