SQL Server Group by Join Strings

In this SQL Server tutorial, we will learn and understand how to use the SQL Server STRING_AGG function and aggregate_functions with JOIN and GROUP BY clauses on tables by the query.

Recently I got a requirement where I need to join strings by concatenation on multiple tables and even group some data in SQL Server. So, I came across the use of SQL Server INNER JOIN statement with the STRING_AGG function and GROUP BY clause.

So, in this SQL Server tutorial, we will go through the following examples where we have used the SQL Server INNER JOIN clause with the STRING_AGG function and GROUP BY clause.

In this tutorial, we will use the ER diagram of the Country’s Geography to join the string with the group by clause.

Here is the list of topics that we are going to cover:

How to use SQL Server Group by Join Strings
Using SQL Server Group By Join Strings Having
How to use SQL Server Group By Join Strings Like
Using SQL Server Group By Join Strings Order by
SQL Server Group By Join Strings Where Clause

Table of Contents

SQL Server Group By Join Strings

Here we will learn and understand how to use the SQL Server STRING_AGG function with the JOIN and GROUP BY clauses on tables in the following query:

EXAMPLE:

SELECT STRING_AGG(CITY.CITY_NAME,' ,') AS JOINING_OF_STATES,
COUNT(CITY.CITY_NAME) AS CITY_PERSTATES,
STATES.STATE_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
GROUP BY STATES.STATE_NAME;

With the help of the INNER JOIN in the SELECT statement, we retrieved the concatenated city names by a comma sign and counted the city name as per the states name from both tables i.e.; CITY and STATES tables.

In the end, we grouped records of the STATE_NAME column from the STATES table for the aggregate function in the result set.

Sql server group by join strings — Example of SQL Server Group By Joins String

We hope you understand how to use the SQL Server STRING_AGG function with the JOIN and GROUP BY clauses on tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Group By Join Strings Having

In this SQL Server subtopic tutorial, we will learn and understand how to use the STRING_AGG function with JOIN and GROUP BY HAVING clauses on tables by the following query:

EXAMPLE:


SELECT STRING_AGG(CITY.CITY_NAME,' ,') AS JOINING_OF_STATES,
COUNT(CITY.CITY_NAME) AS CITY_PERSTATES,
STATES.STATE_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
GROUP BY STATES.STATE_NAME
HAVING COUNT(CITY.CITY_NAME) >2;

With the help of the INNER JOIN in the SELECT statement, we retrieved the concatenated city names by a comma sign. Then we counted the city name whose value is greater than 2 as per the states name from both tables i.e.; CITY and STATES tables.

Sql server group by join strings having example — Example of SQL Server Group By Join Strings Having

In the end, we grouped records of the STATE_NAME column from the STATES table for the aggregate function in the result set.

Read: SQL Server Right Join Distinct

SQL Server Group By Join Strings Like

Let’s see an example to understand the subtopic “SQL Server Group By Join Strings Like” by using the STRING_AGG function with the JOIN and LIKE also GROUP BY clauses on tables by the following query:

EXAMPLE:

SELECT STRING_AGG(AMBASSADORS.AMBASSADORS_NAME,' ,') AS STRING_JOINING,
COUNT(AMBASSADORS.AMBASSADORS_NAME) AS AMBASSADORS_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM AMBASSADORS
INNER JOIN COUNTRIES
ON AMBASSADORS.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME LIKE '%a'
GROUP BY COUNTRIES.COUNTRY_NAME;

With the help of the INNER JOIN clause, first, we concatenated ambassadors’ names by using the comma keyword then we counted ambassadors’ names based on the country’s name from both tables i.e.; AMBASSADORS and COUNTRIES tables.

In the WHERE condition, we have filtered the country’s name ending with the letter a from the COUNTRIES table for the result set.

Sql server group by join strings like sample example — Example of SQL Server Group By Joins Strings Like

By using the SQL Server STRING_AGG function with JOIN and LIKE GROUP BY clauses on tables by the query, we hope you have a better understanding of the subtopic “SQL Server Group By Join Strings Like.”

Read: RIGHT JOIN in SQL Server

SQL Server Group By Join Strings Order by

Let’s see an illustrated example of SQL Server STRING_AGG function with JOIN and GROUP BY ORDER BY clauses on tables by the following query:

EXAMPLE:

SELECT STRING_AGG(CITY.CITY_NAME,' ,') AS CITY_JOINING,
COUNT(CITY.CITY_NAME) AS CITY_PER_COUNTRY,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME
ORDER BY CITY_PER_COUNTRY DESC;

With the help of the INNER JOIN in the SELECT statement, we retrieved and joined the city name by comma sign and then we counted the count of city names per country name from both tables i.e.; CITY and COUNTRIES tables.

In the end, we also arranged the records for the count of city names per country names in descending order for the result set.

Sql server group by join strings order by sample example — Example of SQL Server Group By Join Strings Order by

We hope you understand how to use the SQL Server STRING_AGG function with the JOIN and GROUP BY ORDER BY clause on tables by the query.

Read: SQL Server Count Join Group By

SQL Server Group By Join Strings Where Clause

In this SQL Server subtopic section, we will see an example of an SQL Server STRING_AGG function with the JOIN, WHERE condition and GROUP BY clause on tables by the following query:

EXAMPLE:

SELECT STRING_AGG(CITY.CITY_NAME, ' , ') AS CITY_JOINING,
COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON COUNTRIES.COUNTRY_ID=CITY.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='North America'
GROUP BY COUNTRIES.COUNTRY_NAME;

With the help of the INNER JOIN clause on the SELECT statement, we have counted city names and state names based on the country names from these tables i.e.; CITY, STATES and COUNTRIES table.

In the WHERE condition, we have filtered the record of those countries which come under the NORTH AMERICA continent and then grouped the records based on the COUNTRY_NAME column from the COUNTRIES table for the result set.

Sql server group by join strings where clause sample example — Example of SQL Server Group By Joins Strings Where Clause

We hope that you have understood the subtopic “SQL Server Group By Join Strings Where Clause” by using the SQL Server STRING_AGG function with the INNER JOIN, WHERE condition, and GROUP BY clauses on tables by the query.

You may also like to read the following SQL Server tutorials.

We now know about the post “SQL Server Group by Join Strings” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

How to use SQL Server Group by Join Strings
Using SQL Server Group By Join Strings Having
How to use SQL Server Group By Join Strings Like
Using SQL Server Group By Join Strings Order by
SQL Server Group By Join Strings Where Clause

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.