DEV Community: Olesia Dudareva

Don't miss out on the new features in SQL Server 16.x

Olesia Dudareva — Sun, 28 May 2023 15:46:52 +0000

The latest version of SQL Server has some exciting updates as well as some very useful new features. Today I would like to look at GREATEST and LEAST, two opposite functions for getting the maximum and minimum values in a list.

GREATEST & LEAST

They respectively return the maximum and minimum values from the list of values. There are some restrictions: the list must contain at least one value; the maximum number of values in the list is 254.
Both functions do not support some data types, for example, cursor, geometry, geography, image, table, ntext, text, xml.

Syntax

GREATEST(value1, value2,…)

LEAST(value1, value2,…)

Input parameters can be, for instance, variables, column names, functions, and any combination of arithmetic, and string operators.

Return data type

The functions return the data type with the highest precedence. If all values in the list are of the same type, it will be returned by the functions. If not, they will try to convert them. As a result, it is important that all values in the list be convertible.

Examples

Constant numbers: the scale = 4 because it is determined by the scale of 4.7890 (the highest precedence data type).

SELECT GREATEST(4.7890, '5.1', N'3');

Output:
-------
 5.1000

SELECT LEAST(4.7890, '5.1', N'3');

Output:
-------
 3.0000

Strings

SELECT GREATEST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
John Smith

SELECT LEAST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
Anna Black

Combination of variables, table column and constant

CREATE TABLE Currency (
    Currency_Name VARCHAR(3),
    Currency_Rate DECIMAL(10,4)
    );

INSERT INTO Currency
VALUES ('USD', 0.82),
    ('USD', 0.8101),
    ('USD', 0.8058);
GO

DECLARE @Default_Rate DECIMAL(10, 4) = 0.8195;

SELECT Currency_Name,
    Currency_Rate,
    GREATEST(Currency_Rate, 0.8000, @Default_Rate) AS GreatestRate,
    LEAST(Currency_Rate, 0.8000, @Default_Rate) AS LeastRate
FROM Currency;
GO

Output:
Currency_Name Currency_Rate GreatestRate LeastRate   
------------- ------------- ------------ ------------
USD                   .8200        .8200        .8000
USD                   .8101        .8195        .8000
USD                   .8058        .8195        .8000

How to pivot data using Dynamic SQL in SQL Server

Olesia Dudareva — Mon, 22 May 2023 14:54:45 +0000

SQL Server PIVOT operator is useful when you know all values which should become columns. But if you do not know what is exactly in the table. How to turn rows into columns?

Dynamic SQL can help with it. Let’s check it on the simple example. There is a table Goods. Number of food items and stores can be unlimited. If it always had only 3 stores and 3 food items, we could use standard PIVOT.

Store	Food	Amount
Store 1	potato	50
Store 5	potato	150
Store 12	potato	300
Store 1	tomato	220
Store 5	tomato	180
Store 12	tomato	60
Store 1	cucumber	500
Store 5	cucumber	10
Store 12	cucumber	90

So we need to turn food items into columns. The result should be like this:

Store	potato	tomato	cucumber
Store 1	50	220	500
Store 5	150	180	10
Store 12	30	60	90

Let’s create a table and insert our data:

CREATE TABLE Goods([Store] VARCHAR(255), [Food] VARCHAR(255), [Amount] INT);

INSERT INTO Goods(Store, Food, Amount)
VALUES
('Store 1', 'potato', 50),
('Store 5', 'potato', 150),
('Store 12', 'potato', 300),
('Store 1', 'tomato', 220),
('Store 5', 'tomato', 180),
('Store 12', 'tomato', 60),
('Store 1', 'cucumber', 500),
('Store 5', 'cucumber', 10),
('Store 12', 'cucumber', 90);

GO

We need two temporary tables for calculations:

/*for turning rows into columns*/
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp 
CREATE TABLE #tmp(Store VARCHAR(255), Food VARCHAR(255))
GO
/*for final result*/
IF OBJECT_ID('tempdb..#result') IS NOT NULL
    DROP TABLE #result
GO

Three variables:

DECLARE @sql NVARCHAR(MAX) = '' /*dynamic string*/
        , @columns NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', ['+ Food +'] INT'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*column names for the final result table*/
        , @summary NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', SUM(['+ Food +']) AS ['+ Food +']'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*summing amount*/

SELECT @columns AS [columns], @summary AS [summary];

Now let’s turn food items into columns and safe it into #tmp table

INSERT INTO #tmp 
SELECT DISTINCT Store, 
REPLACE(STUFF(ISNULL((SELECT DISTINCT ', 0 AS ['+ Food +']'
            FROM Goods rw
            FOR XML PATH('')),''),1,2,''),'0 AS ['+ Food +']', CAST(org.Amount AS VARCHAR) + ' AS ['+org.Food+']')
FROM Goods org;

SELECT * FROM #tmp;

And now we need to create columns from our column Food. For this purpose we will use dynamic SQL.

--create final table according to number of columns from @columns and insert data from #tmp
SET @sql = N'create table #result(food varchar(255),'+@columns+'); '+CHAR(10)+
            'insert into #result '+CHAR(10);

SELECT @sql = @sql + N'select '''+Store+''' as Store, '+Food+' union all '+CHAR(10) FROM #tmp;

--remove last 'union all'
SET @sql = REVERSE(STUFF(REVERSE(@sql),1,11,''));

SET @sql = @sql + N'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

The result of sp_executesql procedure will be

If you need to keep it, you can create an additional table and insert data into it. For example:

SET @sql = @sql + N'create table final_result(food varchar(255),'+@columns+');'+CHAR(10)+
                    'insert into final_result'+CHAR(10)+
                    'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

SELECT* FROM final_result;

Also you can add additional columns such as total amount, total count in columns or rows when you turn data. It is quite flexible approach.

Note: if your SQL Version is higher than 2017, it is possible to use STRING_AGG() function to simplify STUFF/XML statements.

Please let me know in the comments below if you use another approaches. It would be interesting to try something new.

How to rotate data using Pivot & Unpivot operators

Olesia Dudareva — Mon, 15 May 2023 10:08:49 +0000

PIVOT and UNPIVOT are Transact-SQL operators which are used for transforming rows to columns and vice versa. These operators were added to SQL Server from 2005 version. Before that there was only one approach how to turn data. It was complex series of SELECT...CASE statements.

PIVOT

PIVOT rotates a table-valued expression by turning the unique values from one column into multiple columns in the output. Also PIVOT runs aggregations on any remaining column values that should be appeared in the final output. PIVOT is useful for visualizing data by years, quarters, months, etc.

Let’s check the PIVOT syntax with a simple example. Below is a table with the earnings of employees from 2022 to 2023 by quarter.

CREATE TABLE Employees (
    Employee_Id INT,
    [Year] INT,
    [Quarter] INT,
    Income MONEY)

INSERT Employees VALUES(1, 2022, 1, 100000)
INSERT Employees VALUES(1, 2022, 2, 100000)
INSERT Employees VALUES(1, 2022, 3, 100000)
INSERT Employees VALUES(1, 2022, 4, 150000)
INSERT Employees VALUES(1, 2023, 1, 100000)
INSERT Employees VALUES(2, 2022, 2, 90000)
INSERT Employees VALUES(2, 2022, 3, 95000)
INSERT Employees VALUES(2, 2022, 4, 90000)
INSERT Employees VALUES(2, 2023, 1, 120000)
INSERT Employees VALUES(3, 2023, 1, 200000)

Employee_Id	Year	Quarter	Income
1	2022	1	100000,00
1	2022	2	100000,00
1	2022	3	100000,00
1	2022	4	150000,00
1	2023	1	100000,00
2	2022	2	90000,00
2	2022	3	95000,00
2	2022	4	90000,00
2	2023	1	120000,00
3	2023	1	200000,00

And now we would like to get only one row for each employee but with data for all years.

SELECT Employee_Id, [2022], [2023]
FROM 
(
    SELECT Employee_Id, [Year], Income FROM Employees
) AS source_query
PIVOT
(
    SUM(Income) FOR [Year] IN ([2022], [2023])
) AS pivoted_table

Employee_Id	2022	2023
1	450000,00	100000,00
2	275000,00	120000,00
3	NULL	200000,00

As you can see, instead of 10 rows we got only three for each employee. PIVOT created two columns [2022] and [2023] from one column [Year] with the aggregated sum of earnings. Aggregation (SUM in the example above) is mandatory in PIVOT.

Also please notice if there is no data for some columns, SQL Server puts NULLs in them as it happened for employee 3 in 2022. But aggregate function inside PIVOT does not consider null values in the value column. As you can see, employee 2 does not have earnings for the first quarter of 2022 but the aggregate function calculated sum correctly.

UNPIVOT

UNPIVOT rotates a pivoted table back by transforming columns into rows. But it does not aggregate values or change them in any way. Also UNPIVOT does not show NULL values. They just disappear in the output.

Let’s take a look at our previous example and create a table from pivoted result set.

CREATE TABLE Pivoted_Employees(
    Employee_Id INT,
    [2022] MONEY,
    [2023] MONEY
)

INSERT Pivoted_Employees VALUES(1, 450000, 100000)
INSERT Pivoted_Employees VALUES(2, 450000, 120000)
INSERT Pivoted_Employees VALUES(3, NULL, 200000)

And now we will try to turn year columns into rows.

select Employee_Id, [Year], Income
from (
    select Employee_Id, [2022], [2023]
    from Pivoted_Employees
) as pivoted_employees
unpivot(
    Income for [Year] in ([2022], [2023])
) as unpivoted_employees

Employee_Id	Year	Income
1	2022	450000,00
1	2023	100000,00
2	2022	450000,00
2	2023	120000,00
3	2023	200000,00

Result set contains only 5 rows because UNPIVOT does not know that original table (before pivoting) had quarters.

In the conclusion, PIVOT and UNPIVOT operators are useful for transforming data when you know how many columns the result set should contain. If not, you need to use more complex structures.