March 23, 2011 at 8:19 am
Hi,
A strange question but bear with me. I have a denormalised table that I am going to query, the query is going to return a subset of rows, however I would like the query to also only include the columns after the subset is created that have data in them. I dont want to have a column which contains all nulls.
I am trying to think about how to do this my best idea so far is some dynamic sql, that first checks the column for data, and then if it does contain data then include within the result.
a bit like IF sum(Case when [TOM] is null then 0 else 1 end) > 1 then @tom,
If you know a better way or can help that would be great.
Thanks,
Oliver
March 23, 2011 at 10:53 am
I would recommend handling this kind of thing outside the database...e.g. pull the resultset into a memory-resident data structure (like an ADO.NET DataTable) then remove all columns from the data structure that only contain NULLs.
However, if you must manipulate the columns returned in your resultset dynamically based on the data in those columns within SQL Server then dynamic SQL is a good place to start.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2011 at 11:22 pm
If you do have to do this in SQL Server you won't be able to do it in a view, as you can't dynamically change the columns a view returns. You can do it in a stored procedure, but I suspect performance will be less than optimal.
I assume you would want to pull the results into a temporary table, then identify the columns that have data and build your dynamic SQL to return the results to the client.
March 24, 2011 at 3:08 am
Thanks for your help on this I will try the ADO.NET route, at the moment I am working with dynamic sql and the table data type.
Cheers,
Oliver
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply