Dynamically selection of Column Name in MSSQL

1119 views sql-server
1

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 a table as under

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)

The output will look like below:

   col1     col2    col3    col4    ColRes  

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

Is it possible ?

answered question

1 Answer

10

Something like:

select col1, min(col2), min(col3), min(col4)
  , max(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
group by col1

posted this

Have an answer?

JD

Please login first before posting an answer.