Add functionality to query
I have some data in SQL Server as:
att1 att2 att3 att4 att5 ... att205
---------------------------------------
438 498 3625 3645 5000 ... 5000
438 498 3625 3648 5000 ... 5040
438 498 3625 3629 5000 ... 5330
437 501 3625 3626 5000 ... 5040
438 498 3626 3629 5000 ... 5050
I want to know the square root of the sum for each column of data, to do so I do:
CREATE VIEW VIEW_myTable (ID, Value) AS (
SELECT 1, SQRT(SUM(att1)) FROM myTABLE
UNION ALL SELECT 2, SQRT(SUM(att2)) FROM myTABLE
UNION ALL SELECT 3, SQRT(SUM(att3)) FROM myTABLE
UNION ALL SELECT 4, SQRT(SUM(att4)) FROM myTABLE
...
UNION ALL SELECT 205, SQRT(SUM(att205)) FROM myTABLE
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTABLE'
Also would like to ADD a extra result
, the number of rows it sumed...
So in the example above there are 5 rows and 205 columns.
How can 开发者_高级运维I take advantage of the scan sql did to the table while suming the elements?
so I do not do something like another SELECT aka.
SELECT COUNT(*) FROM [myTABLE]
In fewer words I want to take advantage of scanning the table in that query... I was thinking to put Something like SUM(1) somewhere but do not know where....
I used the FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTABLE'
because I am doing dynamic sql... but the query looks like that...
----------------EDIT----------------------
As answered by @Neil Moss:
if I would have lots of columns, more than 1024... SQL will not support this... Is there a way to overcome this situation?
If you are prepared to accept the query returning a single row, with a SqrtSumAttrX column for each AttrX column, then dynamically constructing the following works just fine:
select
sqrt(sum(attr1)) as SqrtSumAttr1,
sqrt(sum(attr2)) as SqrtSumAttr2,
sqrt(sum(attr3)) as SqrtSumAttr3,
...
sqrt(sum(attr205)) as SqrtSumAttr205,
sum(1) as RowsScanned
from
MyTable
This has the bonus of only scanning the table once, whereas the sample in your question scans it 205 times - once for each union.
Input:
Attr1 Attr2 Attr3
1 2 3
4 5 6
7 8 9
10 11 12
Output:
SqrtSumAttr1 SqrtSumAttr2 SqrtSumAttr3 RowsScanned
4.69041575982343 5.09901951359278 5.47722557505166 4
EDIT post-acceptance
For dynamic construction, try this:
declare @columns nvarchar(max)
declare @sql nvarchar(max)
set @columns = ''
select
@columns = @columns + 'sqrt(sum([' + [COLUMN_NAME] + '])) as SumSqrt' + [COLUMN_NAME] + ','
from
[INFORMATION_SCHEMA].[COLUMNS]
where
TABLE_NAME = 'MyTable'
and
DATA_TYPE in ('int', 'decimal', 'float') -- numerics only (add other datatypes as needed)
order by
ORDINAL_POSITION
set @sql = 'select ' + @columns + 'count(*) as RowsScanned from Results'
exec (@sql)
I've used count(*)
rather than sum(1)
as suggested by Andriy M as this returns 0 if no rows exist, rather than null.
精彩评论