Today I want to share an interesting example that describes how to find the maximum of multiple columns . Here is a filled table that contains the weekly sales report:
It is necessary to find the maximum number of sales for each product across table columns . In our case, each column is a day of the week. I highlighted in red the values that should be obtained in our sample. To solve this problem, we will use the Table Value Constructor construction, which will allow us to form a temporary table from our columns .
The result should be:
- Laptop 15
- Mouse 24
- Monitor 11
Here is a query:
SELECT [Id] ,[Product] ,( SELECT MAX(SalesCount) FROM (VALUES([Monday]), ([Tuesday]), ([Wednesday]), ([Thursday]), ([Friday]), ([Saturday]), ([Sunday])) AS CurrentWeekTbl1(SalesCount) ) as MaxSales FROM [YarkulTestDb1].[dbo].[CurrentWeekSales]
How does Table Value Constructor Works?
In order to better understand how ‘Table Value Constructor’ works, I will demonstrate it in a lighter example. Let’s assume we need to create a temporary selection of Ukrainian cities:
SELECT * FROM (VALUES ('Kiev'),('Kharkiv'),('Lugansk'),('Lviv'),('Donetsk'), ('Simferopol'), ('Mariupol')) as MyTable(MyColumnA)
From Microsoft documentation: “ Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT … VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause.“