Here is my table
I would like to select as below
The tch_col will get the column name which column value is not 0 and if it's all 0 give the value as 19.
Could we do this is in SQL?
pls check lane 97 here 
the result should 
The first answer maybe not works but still Thank you
From discussion in comments: this is for SQL Server; so using a view to create the 19 column followed by the UNPIVOT operator should simplify things.
Original answer for standard SQL follows for posterity:
You can get the result you describe (with most major SQL databases; if you're using something old or obscure this may not work).
But it's not quite as pretty as selecting the column name. The following SQL is dependent on which specific columns are in the table; see notes afterward.
If you need a record for every non-0 value, it's going to be quite bad.
SELECT tch_function, tch_part, 9
FROM your_table
WHERE tch_col_09 <> 0
UNION ALL
SELECT tch_function, tch_part, 10
FROM your_table
WHERE tch_col_10 <> 0
-- you can see where this is going, right?
-- union in another select for each column
The problem here is that standard SQL isn't good about creating an undetermined number of result rows from each input row, other than during a join.
Now if you can get your hands on a relation with a single column containing the numbers from 9 through 19, you could do a cross join with that and then filter out the rows you don't want (WHERE (your_table.tch_col_09<>0 AND ref.value=9) OR ... OR (your_table.tch_col_09=0 AND ... AND your_table.tch_col_18=0 AND ref.value=19)); but it's still cumbersome, isn't it? You could use a DB2 VALUES subquery, or a temp table, or whatever to get such a relation if you decide to go that route...
If you could assume that only one column has a non-0 value, or if you'd be happy with just the name of the column containing the first non-0 value, it'd be less ugly:
SELECT tch_function
, tch_part
, case when tch_col_09 <> 0 then 9
when tch_col_10 <> 0 then 10
when tch_col_11 <> 0 then 11
when tch_col_12 <> 0 then 12
when tch_col_13 <> 0 then 13
when tch_col_14 <> 0 then 14
when tch_col_15 <> 0 then 15
when tch_col_16 <> 0 then 16
when tch_col_17 <> 0 then 17
when tch_col_18 <> 0 then 18
else 19
end as tch_col
from your_table
The case structure will return the value for the first matching THEN clause, or 19 if none of the THEN clauses match.
But it sounds like that won't do.
Lastly - Any of these queries would have to change if the set of TCH_COL_n columns in the table were to change; at a minimum a good relational table structure should lead to a fairly static set of columns.
More to the point, if you could change your table structure or use a processing language other than SQL you'd be in better shape.
Using a UNION to collate multiple entries
SELECT tch_function,tch_part,9 AS tch_col FROM TCH WHERE tch_col_09 > 0
UNION
SELECT tch_function,tch_part,10 AS tch_col FROM TCH WHERE tch_col_10 > 0
UNION
SELECT tch_function,tch_part,11 AS tch_col FROM TCH WHERE tch_col_11 > 0
UNION
SELECT tch_function,tch_part,12 AS tch_col FROM TCH WHERE tch_col_12 > 0
UNION
SELECT tch_function,tch_part,13 AS tch_col FROM TCH WHERE tch_col_13 > 0
UNION
SELECT tch_function,tch_part,14 AS tch_col FROM TCH WHERE tch_col_14 > 0
UNION
SELECT tch_function,tch_part,15 AS tch_col FROM TCH WHERE tch_col_15 > 0
UNION
SELECT tch_function,tch_part,16 AS tch_col FROM TCH WHERE tch_col_16 > 0
UNION
SELECT tch_function,tch_part,17 AS tch_col FROM TCH WHERE tch_col_17 > 0
UNION
SELECT tch_function,tch_part,18 AS tch_col FROM TCH WHERE tch_col_18 > 0
UNION
SELECT tch_function,tch_part,19 AS tch_col FROM TCH
WHERE tch_col_09 = 0 AND tch_col_10 = 0 AND tch_col_11 = 0
AND tch_col_12 = 0 AND tch_col_13 = 0 AND tch_col_14 = 0
AND tch_col_15 = 0 AND tch_col_16 = 0 AND tch_col_17 = 0
AND tch_col_18 = 0
ORDER BY 1,2
