Skip to content
Advertisement

Dynamically selection of Column Name

I want to select Column Name which has non null and non zero value. So any column name which has 0 or NULL should not be displayed in the output grid

I have the Source table as below

col1    col2    col3    col4    col5D   Col6D   Col7D   Col8D

Abc     0       1       John    0       0       0       0       
Abc     1       2       John    1       0       0       0
Abc     2       3       John    0       0       0       0
Abc     3       4       John    0       1       0       0
Xyz     0       1       Ron     0       0       0       0       
Xyz     1       2       Ron     0       0       0       0
Xyz     2       3       Ron     0       0       1       0
Xyz     3       4       Ron     0       0       0       0

I want to group by Col1 and Display Col1, MIN(Col2), MIN(Col3), MIN(Col4) and ColRes as (Name of the column with value more than 0)

Expected Output as below:

   col1     col2    col3    col4    ColRes  

    Abc     1       2       John    Col5D
    Abc     3       4       John    Col6D
    Xyz     2       3       Ron     Col7D

Is it possible ?

Advertisement

Answer

Something like:

select col1, col2, col3, col4
  , case when col5D > 0 then 'col5d' when col6D > 0 then 'col6d' when col7D > 0 then 'col7d' when col8D > 0 then 'col8d' else '' end
from table1
where col5d > 0 or col6D > 0 or col7D > 0 or col8D > 0
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement