Return cols with values
-
Hi I am having some trouble trying to figure out this sql query or should I be trying to do this with c# and not sql, I have a database table with 20 columns but I only want to return the columns that have values in at least one of their rows, so if a column consists entirely of null values I do not want to return it. I’m just wondering if anyone has any help or advice with the sql or whether or not its possible.
-
Anything wrong with the good old,
WHERE col1 IS NOT null
AND col2 IS NOT null
AND col3 IS NOT null
...
AND col20 IS NOT null
-
unfortunately that wont work because if any of the columns for a single row are null then the entire row is excluded from the results but the other columns in that row will have values that need to be returned
-
Ah, silly me, that should have been OR, not AND
-
this is the sql im workin with at the moment but if i include the or statement the results will include tests that are not on the specified date
Select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
BodyWeight, FourSiteSkinfold_mm,
FourSiteSkinfold_pct,
SquatJump,
CounterMovementJumpWithoutArms,
CounterMovementJumpWithArms,
TenMAcceleration,
TwentyMAcceleration,
ThirtyMSpeed,
EighteenM321Aerobic_TimeInRedZone,
Fifteen_35m321Anaerobic_TimeInRedZone,
BWSquatPower,
MaxHeartRate,
PremierFitness,
DistanceCovered,
HSR,
Sprint,
PF_16_30,
PF_8_120,
Agility,
HighSpeedRunOut,
SprintOut,
[BodyFat(12site)],
[Agility(R)],
[Agility(L)],
HydrationScore,
From TrainingLog tl
Join Person p on p.ID = tl.PersonID
Join TrainingFitnessTesting tft on tft.TraininglogId = tl.ID
Where SessionTypeID = 1
And tl.Date = '01/16/2006'
-
You could try adding
AND COALESCE(column,column,column) IS NOT NULL
to the end of your query.
Just list the columns you want to check between the brackets.
Far away is close at hand in the images of elsewhere.
Anon. -
no afraid not the null columns are still returned
-
How about:
Where SessionTypeID = 1
And tl.Date = '01/16/2006'
AND (
col1 IS NOT null
OR col2 IS NOT null
....
OR col20 IS NOT null
)
-
no afraid not
to be honest folks i dont think this can be done usig sql as i need to specify the columns at the start of the statement so how can they not be in the final results then
-
Ah, so if the entire column (not row) doesn't have any values for that field then you wan't to hide the column, that sounds more like something that should be part of the client application rather than the server otherwise anything that expects those columns to always be there would error when they were missing.
-
im thinkin(hopin) it can be done using c# because all i have to do is display the results in a datagrid
-
It can, you just have to set up the table styles, and set each column style's visible property to false for the ones you don't want to show up. That or just not include that column in the table style altogether.
-
any ideas how i could work out which cols i need to make invisible
-
It can, you just have to set up the table styles, and set each column style's visible property to false for the ones you don't want to show up. That or just not include that column in the table style altogether. This and the original post (realised after rereading
) only possible with a prefetch, whether in sql or client does not matter (how do you know whether to suppress the column without accessing all the data in that column 
)Possible using dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = N'SELECT IsNull(p.firstName+ '' '','''') + IsNull(p.MiddleName+ '' '','''') + IsNull(p.LastName,'''') as [Player]'
IF EXISTS(SELECT *
FROM TrainingLog tl
JOIN Person p on p.ID = tl.PersonID
JOIN TrainingFitnessTesting tft on tft.TraininglogId = tl.ID
WHERE SessionTypeID = 1
AND tl.Date = '01/16/2006'
AND BodyWeight IS NOT NULL)
SET @sql = @sql + N',BodyWeight'
SET @sql = @sql + ' FROM TrainingLog tl
JOIN Person p on p.ID = tl.PersonID
JOIN TrainingFitnessTesting tft on tft.TraininglogId = tl.ID
WHERE SessionTypeID = 1
AND tl.Date = ''01/16/2006'''
EXEC sp_executesql @sql
(repeat the IF EXISTS for each column to be tested/included)
This would be slow due to the repeated query.
Could speed up by counting non null values for each column in one pass and then testing the results.
Far away is close at hand in the images of elsewhere.
Anon. -
If you're not returning thousands of rows it may be better just to have the client calculate this.
Something as simple as using System.Collections.Specialized.BitVector32, this will effectivly give you 32 boolean values that can be accessed by index.
Set the values to false initially (not visible), then loop through every row, for each column that you find a non null value set it's value to true.
Just make sure to handle changing of column indexes in the query compared to indexes in the bit vector, something like:
Const int idx_style_column_name = 0;
...
BitVector32 has_value = new BitVector32();
int idx_column_name = reader.GetOrdinal("column_name");
...
if (!reader.IsDbNull(idx_column_name)) has_value[idx_style_column_name] = true;
The reapeated parts could be generated via a few excel functions, a very useful code generator.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply