Return cols with values
-
Depending on the cost of the query, maybe you can do this in a stored proc :
DECLARE @OutputColumns AS VARCHAR(1000) OUTPUT
CREATE TABLE #temp
Insert into #temp
YourQueryHere
Then scan the resultSet
I'm not sure this statement is legal but you'll get the idea :
SET @OutputColumns = ''
Select @OutputColumns = CASE WHEN Col1 > 0 THEN 'Col1, ' ELSE '' END,
@OutputColumns = @OutputColumns + CASE WHEN Col2 > 0 THEN 'Col2, ' ELSE '' END,
@OutputColumns = @OutputColumns + CASE WHEN Col3 > 0 THEN 'Col3, ' ELSE '' END
(SELECT COUNT(Col1) AS Col1, COUNT(Col2) AS Col2, COUNT(Col3) AS Col3 FROM #temp) dtA
Select * FROM #Temp
--the list of non null columns is already outputted with the output param so all you have to do is hide the columns that are not listed in that parameter (might be simpler to list the columns with all null sand hide those instead of the reverse).
-
You'd have to loop thru all the columns and perform an aggregaite check.
I'll post back in awhile.
-
Declare @ColumnName varchar(128), @SQL varchar(8000)
, @NonNULLColumns varchar(1000)
Set @NonNULLColumns = ''
Set @SQL = ''
select Column_name INTO #NonNullColumnNames
from information_schema.columns
where 1=2
Declare ColumnNamesCursor CURSOR FOR
select distinct Column_Name from information_schema.columns
where table_name IN ('TrainingLog', 'Person', 'TrainingFitnessTesting ')
AND IS_Nullable = 'YES'
AND Column_Name IN ('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')
Open ColumnNamesCursor
Fetch Next from ColumnNamesCursor
Into @ColumnName
While @@Fetch_status = 0
Begin
Set @SQL = 'Select distinct ''' + @ColumnName + '''
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''
group by ' + @ColumnName + '
having Count(' + @ColumnName + ') <> 0'
Insert #NonNullColumnNames
EXEC(@SQL)
Fetch Next from ColumnNamesCursor
Into @ColumnName
End
Close ColumnNamesCursor
Deallocate ColumnNamesCursor
Select @NonNULLColumns = @NonNULLColumns + Column_name + ', ' from #NonNullColumnNames
Set @SQL = 'Select ' + Left(@NonNULLColumns, (Len(@NonNULLColumns) - 1)) + '
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'''
Print @SQL
Exec(@SQL)
drop table #NonNullColumnNames
-
If the column names are known at design time, then all that dynamic sql is not needed. But then again maybe I forgot the requirements... again
.Also a single scan of the results is enough to get all non nulls columns, no need to scan once per column.
-
Yeah, you don't really need this much. I just made the design flexible for future use.
Also, the script will readily work for him when copied and pasted in QA.
I did, however, highlighted the gist of the script. It should get tadhq88 goin'.
Also a single scan of the results is enough to get all non nulls columns, no need to scan once per column.
-- YOU'RE right about that one.
-
"-- YOU'RE right about that one.
". I like the sound of that
-
Merry Christmas! ß Click Here
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply