how to get first column that contains value >0

how to get first column that contains value >0

Type      Dec 09      Mar 10      Jun 10      Sep 10      Dec 10      Mar 11      Jun 11 
A    (0.07)  (0.11)  0.00    (0.01)  0.10    0.02    0.22 

I 开发者_JAVA百科have data like above i want to find 1) first column which contains >0 value- (for above e.g. ans should be Jun 10) 2) first column which contains >0 value & also the next column contains >0 value- (for above e.g. ans should be Dec 10)

Hoping (I really really hope) your table is as follows and you have transposed the data for whatever reason (and Dec 09 etc are not names of columns):

---------------
Type     A
---------------
Dec 09   (0.07)
May 10   (0.11)
Jun 10   0.00
Sep 10   (0.01)
Dec 10   0.10
Mar 11   0.02
Jun 11   0.22

MySql:

select *
  from table
 where A= 0
 order by type 
limit 0, 2

In MS SQL Server 2005 and later you can use the system DMVs sys.tables and sys.columns to obtain the list of the columns for this table and then iterate using cursor. This is ugly, but working solution. list of columns you can obatin like this:

SELECT sys.columns.name from sys.columns INNER JOIN sys.tables ON sys.columns.object_id 
= sys.tables.object_id
WHERE sys.tables.name='TableName'

You would have to write a complex and specific expression to do that. As you have data in your field names, there is no construct in SQL to easily query this data.

Something like, and that's only to get the first date:

select
  Type,
  case
    when [Mar 10] > 0 then [Mar 10]
    when [Mar 11] > 0 then [Mar 11]
    when [Jun 10] > 0 then [Jun 10]
    when [Jun 11] > 0 then [Jun 11]
    when [Sep 10] > 0 then [Sep 10]
    when [Dec 09] > 0 then [Dec 09]
    when [Dec 10] > 0 then [Dec 10]
  end
from TheTable

You data should instead be arranged like this:

Type   Date          Value
A      2011-12-09    -0.07
A      2011-05-10    -0.11
A      2011-06-10    0.00
A      2011-09-10    -0.01
A      2011-12-10    0.10
A      2011-03-11    0.02
A      2011-06-11    0.22

Then you can write a query that doesn't have to know which dates there are. Something like:

select x.Type, x.Date, t.Value
from TheTable t
inner join (
  select Type, min(Date) as Date
  from TheTable
  where Value > 0
  group by Type
) x on x.Type = t.Type and x.Date = d.Date