SQL can I select column name as value

Question

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

Answer 1

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.

Answer 2

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