Using the Values() clause in T-SQL to get max() and sum() of values

Values() clause also known as Table valued Constructor(TVC) was introduced in SQL server 2008. This is a very useful clause and has variety of applications. One such application we will be looking here is to create a matrix of numbers and then display the sum of numbers and maximum values in each row of the matrix using values() clause. SQL code is as below:

Declare @table table(t1 int, t2 int, t3 int) –Declare table variables t1, t2, t3
Insert @table values(1,2,3),(4,5,6),(7,8,9) –Insert values into these table variables
Select *,
–values() clause is used to retriev the values and then applying sum() function to get the sum of values across rows
(Select sum(val) from(Values(t1),(t2),(t3))value(val))sum_value,
–values() clause is used to retriev the values and then applying Max() function to get the maximum of values across rows
(Select max(val) from(values(t1),(t2),(t3))value(val))max_value
from @table

Result:
t1 t2 t3 sum_value max_value
1  2   3          6              3
4  5   6          15            6
7  8   9          24            9

Leave a Reply

Your email address will not be published. Required fields are marked *