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 😊)