July 5, 2010 at 2:28 am
assalam o alaikum
I want to find non-zero minimum value in each numeric column in a table in SQL Server Database. i have a table with about 50 columns and i want to find the non-zero minimum value from each column using one quer/store procedure. The data is in the following form.
col1 col2 col3 ..... col50
0 2 3 2
1 0 1 0
2 -1 0 1
now i want the follwoing output
col1 col2 col3 ..... col50
1 -1 1 1
any help will be appreciated.
Regards,
Kamran
July 5, 2010 at 2:48 am
Hi there, have this :
declare @table table
(
c1 int, c2 int , c3 int, c4 int, c5 int
)
insert into @table
select 1,2,3,4,5
union all select 0,9,-1,3,-5
union all select 21,1,-1,20,-1
union all select -21,1,1,0,0
union all select 2,0,0,0,0
;with cte as
(
select
/* case when sum(c1) <> 0 then nullif(c1,0)
else sum(c2) end non_zero_c1,
case when sum(c2) <> 0 then nullif(c2,0)
else sum(c2) end non_zero_c2 ,
case when sum(c3) <> 0 then nullif(c3,0)
else sum(c3) end non_zero_c3 ,
case when sum(c4) <> 0 then nullif(c4,0)
else sum(c4) end non_zero_c4 ,
case when sum(c5) <> 0 then nullif(c5,0)
else sum(c5) end n on_zero_c5 */
nullif(c1,0) non_zero_c1,
nullif(c2,0) non_zero_c2,
nullif(c3,0) non_zero_c3,
nullif(c4,0) non_zero_c4,
nullif(c5,0) non_zero_c5
from @table
)
select min(non_zero_c1) c1 ,
min(non_zero_c2) c2 ,
min(non_zero_c3) c3 ,
min(non_zero_c4) c4 ,
min(non_zero_c5) c5
From cte
But i have got a doubt here ; What if all the rows for a column is zero ?? what will be the minimum of that column then ?
July 5, 2010 at 4:02 am
thank you much for your response. but what if i want a more generic way of doing this. i mean is there a way to do this without replacing the 0 values first with nulls because it requires to mention all the column names explicitly and also one has to do this for every table when one wants to do this (selecting non-zero) values from all the fields of a table with varying number of fields.
Regards,
Kamran
July 5, 2010 at 5:15 am
Try this:
DECLARE @TblName NVARCHAR(1000)
SET @TblName = 'YourTableName'
DECLARE @sql NVARCHAR(MAX)
SET @sql = ' SELECT ''Minimal Values'''
SELECT @sql = @sql + ', MIN([' + isc.COLUMN_NAME + ']) AS ' + ' [MinOf' + isc.COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS isc
WHERE isc.TABLE_NAME = @TblName
AND isc.NUMERIC_PRECISION IS NOT NULL
SET @sql = @sql + ' FROM ' + @TblName
EXEC (@SQL)
Look up BoL for using sp_executesql and learn how to use it instead of EXEC (@sql) to avoid SQL injection...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply