Hiding zero collumns
-
I have this result from a SQL command
year Mb Ca Si Ca Cu Nq Tg
----------------------------
1999 0 4 55 0 2 0 1
2000 1 7 88 0 3 0 1
I need the some SQL commands what brings the following result:
year Mb Ca Si Cu Tg
---------------------
1999 0 4 55 2 1
2000 1 7 88 3 1
Observe what the Ca e Nq collumns was not showed because his sum is equal zero.
How do I make this?
Thanks a lot again.
Alex Sandro
-
Simple solution > do it application side.
Other solution. Prerun the query like this :
Select case when sum(dtName.Col1) = 0 then 'Col1' else null as Col1,
case when sum(dtName.Col2) = 0 then 'Col2' else null as Col2....
from (Primary select statemet) dtName
Then use that select to not show those columns at the application side.
-
Thanks for the help !
But in my case, it doesnt works. I need to what the collumns with sum is equal zero dont came in the result of the sql command.
Its because the application what will to show the results dinamically create the grid with the columns from the SQL command. If the result came with 84 collumns, the application will show 84 collumns.
Some case, I just need to see 5 of them. How I saied before, I really need what the other 79 collumns dont came in the SQL result.
Some sugestion...
Once more time thanks.
-
Prerun the query to find the columns to hide.
Remake the sql statement with dynamic sql and run that statement. This will be a pain to code really.
-
I have used ALTER TABLE to insert columns into a #TempTable using Dynamic SQL in the past. That may be a possible approach to ease the pain. I agree with Remi that this will not be easy...
I wasn't born stupid - I had to study.
-
Just a newbie question here... can't the columns of datagrid be manipulated in some way?? I know we can change the fonts/colors... but is there a way to change the size or visibility? I think it would be much simler than to try to do this.
-
I know from my past with this, grids are often third party components and a real bear to alter. I whole-heartedly agree with Remi, though. If you can do this on the Client-side, you will be much happier with the results.
I wasn't born stupid - I had to study.
-
It sounds as though you'll need to query for each column separately, then use that to build your field list for a dynamic SQL statement.
Loop through fields
Is field sum 0
If not add field to SQL string
Call sp_executeSQL with your SQL string.
-
-- get colnames
Select @STR= case when sum(dtName.Col1) <> 0 then 'Col1' else '' end +
case when sum(dtName.Col2) <> 0 then ',Col2' else '' end +
case when sum(dtName.Col3) <> 0 then ',Col3' else '' end + ...
from (Primary select statemet) dtName
-- Check for initial comma
if Left(@str,1) = ','
set @STR = Substring(@str,2,len(@str)-1)
--execute it
exec ( 'select ' + @STR + 'from Yourselectstatement' )
* Noel -
Now you're agreeing with us
. -
Well, most of the time I DO
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply