Summary of SQL built-in functions

BETWEEN value1 and value2

The BETWEEN function in SQL is used to filter the result set within a specified range. It can be used with numeric, date, or textual values. Here’s a basic example:

SELECT *
FROM employees
WHERE age BETWEEN 25 AND 35;
STRING_AGG ( column_name, ”, ‘ )

The STRING_AGG() function in T-SQL (Transact-SQL) is used to concatenate values from multiple rows into a single string, separated by a specified delimiter. It’s particularly useful for combining text from multiple rows into a single row result.

SELECT STRING_AGG(column_name, ', ') AS concatenated_column
FROM table_name;
  • column_name is the name of the column containing the values you want to concatenate.
  • , is the delimiter that separates the values in the resulting string.
select id, AppDate,Company from cv where id between 270 AND 280
id	AppDate	Company
270	2021-04-24	dentalcorp  
272	2021-04-24	EMHware
274	2021-04-24	Altus Group  
276	2021-04-24	Dawn InfoTek Inc.
278	2021-04-25	Capco
280	2021-04-25	OPTrust  

select string_agg([Company],',') from cv where id between 270 AND 280 
concated
dentalcorp,EMHware,Altus Group,Dawn InfoTek Inc.,Capco,OPTrust  
Concatenating Text from Multiple Rows

For instance, suppose you have a table of employees with a column for their skills, and you want to get a list of all skills each employee has:

SELECT employee_id, STRING_AGG(skill, ', ') AS skills
FROM employee_skills
GROUP BY employee_id;
Generating a Comma-Separated List of Values

If you have a table of orders and you want to list all products in each order:

SELECT order_id, STRING_AGG(product_name, ', ') AS products
FROM order_details
GROUP BY order_id;
Creating a Summary Report

You can use STRING_AGG() to generate a summary report, for example, listing all customers in each country:

SELECT country, STRING_AGG(customer_name, '; ') AS customers FROM customers GROUP BY country;
Generating Dynamic SQL Queries
DECLARE @sql NVARCHAR(MAX); SELECT @sql = STRING_AGG('SELECT * FROM ' + table_name, ' UNION ALL ') FROM tables_to_query; EXEC sp_executesql @sql

Please do not hesitate to contact me if you have any questions at William . chen @ mainri.ca

(remove all space from the email account 😊)