October 9, 2002 at 7:52 am
Is there a way to calculate STDEV across several columns in a stored proc or UDF?
i.e.: STDEV(Col1, Col2,Col3, Col4)
Or, if that is not possible to convert columns to rows, calculate and convert back?
Thanks,
Jakub
P.S. By the way, the search page on this site doesn't seem to work. Does it rely on cookies or something?
October 10, 2002 at 5:27 am
-- Columns into Rows:
-- Conditions:
-- will need to read from Table with standard column headers
-- Will need to specify which RowId to read
-- Will need to specify number of columns
-- create inital table
create table #Main
(
Col1 int,
Col2 int,
Col3 int,
Col4 int,
Col5 int
)
-- populate
insert into #Main values (2,5,9,10,4)
-- create second temp table
create table #STDevTest
(
Value int
)
-- declare variables
declare @rowId int
declare @NoOfColumns int
declare @counter int
declare @sql varchar(1000)
set @counter = 1
-- Set RowId and number of columns
set @rowId = 1
set @NoOfColumns = 5
while @counter <=@NoOfColumns
begin
set @sql = 'insert into #STDevTest (value) select Col' +
cast(@counter as varchar(10)) +
' from #Main'
exec (@SQL)
set @counter = @counter + 1
end
select stdev(value) from #STDevTest
drop table #Main
drop table #STDevTest
October 10, 2002 at 4:33 pm
That's what I was looking for.
Thanks,
I am using table variable instead the Temp table.
Thanks again,
Jakub
October 11, 2002 at 4:41 am
You may find you have a problem using table variables in dynamic sql. I did try this but found a temp table the only option. Table variables have several limitations, and declaring them, and then refering to them in dynamic Sql seems to be one of them!
October 11, 2002 at 9:30 am
What limitations are there to table variables? I thought that they are a preferred way to temp tables.
Jakub
October 14, 2002 at 2:38 am
I have found that if you declare a table variable, and then try to use it in a dynamic piece of SQL later on, it will return a 'must declare the variable' error message. The only way I have found to use table variables in dynamic SQL is to declare them in the dynamic part that refers to it.
This may be it's only limitation, as I don't think I have come across any more (apart from the fact that you should be wary about adding extremely large amounts of data into it as it holds it all in memory).
David
October 14, 2002 at 7:47 am
Hi
I don't know if this helps you. If you have many fields whose data are in consideration to find the std. deviation, then this is one possiblity.
select stdev(a) from (select col1 as a from tab union select col2 as a from tab union select col3 as a from tab) x
Thanks and regards,
Anbu
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply