Sql Server: apply an aggregate function to fields values in a row

Did you ever need to apply an aggregate function to fields values in a row? It happened to me just few days ago…

I will try to explain better what I mean…

Suppose you have a Sql Server table created with the following script:

CREATE TABLE ROW_AGGREGATE_EXAMPLE
(
   ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
   FIELD1 DECIMAL(8, 2),
   FIELD2 DECIMAL(8, 2),
   FIELD3 DECIMAL(8, 2),
   FIELD4 DECIMAL(8, 2),
   FIELD5 DECIMAL(8, 2),
   FIELD6 DECIMAL(8, 2),
   FIELD7 DECIMAL(8, 2),
   FIELD8 DECIMAL(8, 2),
   FIELD9 DECIMAL(8, 2),
   FIELD10 DECIMAL(8, 2)
)

Let’s populate the table with some test data:

INSERT INTO ROW_AGGREGATE_EXAMPLE(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9, FIELD10)
VALUES(5.8, 5.1, 7896.1, 0.54, 3.41, 4.64, 1.25, 1.28, 9.98, 1.98)

INSERT INTO ROW_AGGREGATE_EXAMPLE(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9, FIELD10)
VALUES(15.8, 135.1, 36.1, 10.54, 0.57, 4.12, 1.50, 90.28, 90.98, 10.98)

INSERT INTO ROW_AGGREGATE_EXAMPLE(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9, FIELD10)
VALUES(50.8, 50.1, 16.1, 85.54, 13.41, 64.64, 81.25, 231.28, 239.98, 9891.98)

INSERT INTO ROW_AGGREGATE_EXAMPLE(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9, FIELD10)
VALUES(534.8, 567.1, 436.1, 760.54, 453.56, 234.40, 881.15, 451.28, 349.98, 45451.98)

INSERT INTO ROW_AGGREGATE_EXAMPLE(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9, FIELD10)
VALUES(8.6, 1.57, 1.60, 540, 33.1, 124.6, 10.50, 10.50, 945.98, 149.98)

Now suppose you want to write a query that extracts for each row the MAX of FIELD1…FIELD10.

How would you do that?

One way is create a scalar valued function with ten parameters and put into the function the logic needed to calculate the MAX among the parameters passed.

But there is also another way…

In Sql Server, certainly from the 2008 version but maybe even earlier, you can build a table with fixed data in this way:

SELECT *
FROM
(
VALUES	(1, 'AAA'),
	(2, 'BBB'),
	(3, 'CCC'),
	(4, 'DDD'),
	(5, 'EEE'),
	(6, 'FFF'),
	(7, 'GGG'),
	(8, 'HHH'),
	(9, 'FFF')
) T(ID, FIELD)

The result of the query above is the following:

ID          FIELD
----------- -----
1           AAA
2           BBB
3           CCC
4           DDD
5           EEE
6           FFF
7           GGG
8           HHH
9           FFF

This feature of Sql Server can be used to solve the problem mentioned at the beginning of this post, and this is the query:

SELECT ID,
   (SELECT MAX(FIELD_VALUE) 
        FROM
        (
            VALUES (FIELD1),
                   (FIELD2),
                   (FIELD3),
                   (FIELD4),
                   (FIELD6),
                   (FIELD7),
                   (FIELD8),
                   (FIELD9),
                   (FIELD10)
        ) AS T(FIELD_VALUE)
   ) AS FIELDS_MAX 
FROM ROW_AGGREGATE_EXAMPLE

And this is the result of the query:

ID          FIELDS_MAX
----------- ---------------------------------------
1           880.741111
2           43.933333
3           1190.185555
4           5518.592222
5           199.258888

Greetings from the dummy programmer!!

Related Posts