Find Columns

[sqlbraindead]

I want to do a search on the column, if the month is Jan in the specified date, then it will return the data in the Jan column, when the specified month is Feb, then it will return the data in the Feb column and so forth for the 12 months of the year. How to code the TSQL for this problem? I wonder if it would do with ARRAY of columns.

[deepuv04]

I want to do a search on the column, if the month is Jan in the specified date, then it will return the data in the Jan column, when the specified month is Feb, then it will return the data in the Feb column and so forth for the 12 months of the year. How to code the TSQL for this problem? I wonder if it would do with ARRAY of columns.

Hope this query will help you

the following query will return you the desired result if you are storing the data in dateformat with which you are comparing the month

select * from Table_Name
where month(Column_Name) = month( Parameter/SpecifiedDate )


if you want to include the year also add one more condition


select * from Table_Name
where month(Column_Name) = month( Parameter/SpecifiedDate ) and
year(Column_Name) = year( Parameter/SpecifiedDate )


thanks

[sqlbraindead]

Hope this query will help you

the following query will return you the desired result if you are storing the data in dateformat with which you are comparing the month

select * from Table_Name
where month(Column_Name) = month( Parameter/SpecifiedDate )


if you want to include the year also add one more condition


select * from Table_Name
where month(Column_Name) = month( Parameter/SpecifiedDate ) and
year(Column_Name) = year( Parameter/SpecifiedDate )


thanks

Thanks a lot deepuv, however, there is still some issues on my database, since one of the table is like this:

TableName: Animal
ID Name
A Cat
B Dog

TableName: Sales
ID Jan Feb Mar Apr May Jun ....
A 1 2 1 4 4 3 ....
B 2 4 3 2 3 1 ....


In this case, if I specify Jan, the output will be like:

ID Name Sales(Jan)
A Cat 1
B Dog 2


When I specify Feb, the output will be something like:

ID Name Sales(Feb)
A Cat 2
B Dog 4

How can I achieve this result?

[deepuv04]

Thanks a lot deepuv, however, there is still some issues on my database, since one of the table is like this:

TableName: Animal
ID Name
A Cat
B Dog

TableName: Sales
ID Jan Feb Mar Apr May Jun ....
A 1 2 1 4 4 3 ....
B 2 4 3 2 3 1 ....


In this case, if I specify Jan, the output will be like:

ID Name Sales(Jan)
A Cat 1
B Dog 2


When I specify Feb, the output will be something like:

ID Name Sales(Feb)
A Cat 2
B Dog 4

How can I achieve this result?

hI,


use the following query

since you are getting the coulumns dynamically, you need to build the query as string and then execute the query to get the result...


DECLARE @String varchar(1000)
DECLARE @STR VARCHAR(10)
SELECT @STR = LEFT(DateName(month, Specify_Date),3)
SET @String = 'SELECT ID,Name,' + @STR+ ' AS Sales('+ @STR +') FROM Sales INNER JOIN
Animal ON Animal.Id = Sales.Id '
Print @String -- Prints the query just for your understanding

EXEC (@String) -- Gets the result you want

thanks

[sqlbraindead]

hI,


use the following query

since you are getting the coulumns dynamically, you need to build the query as string and then execute the query to get the result...


DECLARE @String varchar(1000)
DECLARE @STR VARCHAR(10)
SELECT @STR = LEFT(DateName(month, Specify_Date),3)
SET @String = 'SELECT ID,Name,' + @STR+ ' AS Sales('+ @STR +') FROM Sales INNER JOIN
Animal ON Animal.Id = Sales.Id '
Print @String -- Prints the query just for your understanding

EXEC (@String) -- Gets the result you want

thanks

Thanks a lot! deepuv! This is really helpful!