SQL Server - Select not null Columns from a single row

Asked By mariner Mariner on 20-Jul-14 02:42 PM
I have got a table with 50 cloumns
I wish to select for a single orw only those columns 
which are having not nul value

For example
Table1 ( ID Int (PK),Col1  Char,Col2  Char,Col3   Char)
The values are
ID   Col1  Col2   Col3
1    A     B       Null
2    C     Null    Null

I want to select fOR id = 1 Only those columns which are not null i.e Col1 , Col2
For Id =2 just Col1

The problem is ihave got 50 columns and  i have no way of no knowing which of these columns is not null

You could - Asked By Peter Bromberg on 21-Dec-01 11:13 AM

by definition all select statements return a fixed number of specified columns. Unless each column were in a separate table with a foregn key index to the primary table, I don't think there's a way to do this in a select statement.

However, you COULD assign the single row's columns to a variable, testing whether each column value IS NOT NULL before concatenating it to the list. 

That should give you a start toward a solution I hope.

Thanks - Asked By mariner Mariner on 20-Jul-14 02:43 PM

i will try to work towards it using ths solution u offered