CODEWITHSUNDEEP
sqlsql-servert-sql
user3115933
user3115933

Reputation: 4453

How to calculate Standard Deviation of a series of numbers in wide format from a table?

I am using SQL Server 2014 and I have table named t1. Extract is given below:

Id   n1  n2  n3  n4  n5  n6
100  3   6   10  11  18  31
101  10  15  16  22  28  37
...

Expected Output:

Id   n1  n2  n3  n4  n5  n6   StdDev
100  3   6   10  11  18  31    10.1
101  10  15  16  22  28  37     9.9
...

Do I necessarily need to pivot table t1 into a long format (and use the STDEV() function) to perform this operation or is there another way it can be handled?

Upvotes: 0

Views: 62

Answers (1)

Zhorov
Zhorov

Reputation: 29983

You may use VALUES table value constructor and APPLY operator:

Table:

CREATE TABLE Data (
   Id int,
   n1 int,
   n2 int,
   n3 int,
   n4 int,
   n5 int,
   n6 int
)
INSERT INTO Data (Id, n1, n2, n3, n4, n5, n6)
VALUES
   (100,  3,   6,   10,  11,  18,  31),
   (101,  10,  15,  16,  22,  28,  37)

Statement: 

SELECT d.*, c.StDev
FROM Data d
CROSS APPLY (
   SELECT STDEV(n) AS StDev
   FROM (VALUES (n1), (n2), (n3), (n4), (n5), (n6)) v (n)
) c

Result:

Id  n1  n2  n3  n4  n5  n6  StDev
100 3   6   10  11  18  31  10.1077528000375
101 10  15  16  22  28  37  9.8725207858310

Upvotes: 2

Related Questions