Retrieve columns with values

[ChanceND]

I need some help with some logic and sql here.

What I have to do is run a query and then retrieve the name of the columns that have a value that is not Null or 0, so in my gridview I can only display the fields that have a value. And I cant use a switch statement or anything like that in my C# code because the sql table layout will be changed from time to time so it wont always have the same fields.

Any sql masters out there know of any ways I could accomplish this either through sql statements or in my C# code?

Thanks

[Brandoe85]

Not sure about Sql, but in C# you can make an sql statement that will do it. First get all of your column names for that table, next loop through your data and add on your clause. So, in psuedo code:

select column names from your table.
initialize your dynamic sql string to: "select * from table where ";

for each column
// here you add on the not null and != 0 part
sql += column + " is not null and " + column + " != 0 AND "
end loop

I can write it up in actual syntax, but I think that gets the point accross.

Something like:

private string generateSQL(string strTable)
{
    string strSql = "select * from " + strTable + " where ";
    SqlConnection con = new SqlConnection(@"your con");
    SqlCommand command = new SqlCommand("select COLUMN_NAME From yourDB.Information_Schema.Columns where table_name = '" + strTable + "'", con);
    con.Open();

    SqlDataReader reader;
    reader = command.ExecuteReader();
    while(reader.Read())
    {
       strSql += reader.GetString(0) + " IS NOT NULL AND " + reader.GetString(0) + " != 0 AND ";
    }
    reader.Close();
    con.Close();

    return strSql.Substring(0, strSql.Length -4);
}

// usage
string strTest = generateSQL("tbl");

Watch out for datatype issues on the != 0 part.
Good luck;