Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

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

Posted on October 5, 2019 By

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!!

 

 

Sql Server, Sql Server 2016

Post navigation

Previous Post: Configure access permission to a folder for an IIS Application pool
Next Post: Visual Studio 2019 error: Failed to create an editor. Value cannot be null. Parameter name password.

Related Posts

Sql Server error using a database with a CLR assembly CLR
Get SQL Server version in T-SQL Sql Server
Sql Server add constraint WITH NOCHECK Sql Server
Getting SQL Server sample databases Sql Server
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Excel
How to know who is blocking a query in Sql Server Sql Server

Search

Related posts

  • Sql Server error: Cannot construct data type date, some of…
  • SQL Server LEN function return 0 passing a string with one…
  • One shot secret function: what's that?
  • Get SQL Server version in T-SQL

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (4)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark