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