table tblA has the follwing data
Row Cols 1,2,3,4,5
1 : 'A', 'B', Null, Null, Null
2 : 'A', 'B', 'C', Null, Null
3 : 'A', 'B', 'C', 'D', Null
4 : 'A', Null, Null, Null, Null

As you can see, the only columns with data are 1,2,3,4. Column 5 is only Null.

As i'm a lazy git, I want to be able to select all the rows I want, but also only the columns that have data. If my select worked, I'd see 4 rows and columns 1 to 4 with 5 missed out because for the qualifying rows, there's no data in it.

anyone know how i can do that? it's been bugging me for about 10 years.

Why am I asking? I have about 50 columns of data that I know will have data in out of 350 and I can't be as$ed to type in select col1, col3, col4, col5.... for 50 or so columns!

is there an easy way?

 

Something on the lines of:

select 1,2,3,4,5
from database
where <> null

or depending on SQL & database

where <row> != null



something on those lines?

 

that's not valid sql and the point is I don't want to type in 50+ column names

more like

SELECT TOP 100 * FROM TABLEA WHERE COLUMNS HAVE DATA IN THEM

 

Is it the same 50 columns you want to display for your entire record set? If so you could generate the SELECT query using some Perl or any language.

for($i=1;$i<=50;$i++) { $cols .= "col$i,"; }
$sql = "select $cols from table...."

Or something.....
Could probably do something similar in T-SQL, if that's your database.......

With regards to filtering the columns that are always null, hmm.... in single SQL statement I don't know. Definitely possible in a script or stored procedure (in the above for loop test for data in the column on the entire table before concatenating it ... could be very slow), or possibly a DB-specific feature.

 

In a word no, standard sql syntax requires to either use the wildcard for all columns or to individually specify the columns you want in your result set. As mentioned above you could build you sql dynamically, if you are using sql server you could build the statement dynamically and use a exec (@<var holding sql statement) to run it. That said sql is geared towards row filtering and not column filtering.

Gary

 

thought so. thanks

i'm just a lazy g!t

 

can you convert the table into a one data column per row with a reference to the column name? That way you could then cross tab the data back out

 

if not you could write a stored procedure that (in sql server for example) could use the system table to pull out the column names for that table, create a temp table and then return the data.