Displaying Multiple Records in a Single Row Using the STUFF Function in SQL Server

[Barry]

We are here with a short article for situations where we need to display multiple records corresponding to one record in a single line during database operations. Let's imagine we have two tables with cities and districts. Let's build the Cities table.

Cities Table
```sql
CREATE TABLE tblCities (
city_ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
city_name VARCHAR(255)
)

INSERT INTO tblCities (city_name) VALUES ('Adana')
INSERT INTO tblCities (city_name) VALUES ('Ad?yaman')
INSERT INTO tblCities (city_name) VALUES ('Afyon')
INSERT INTO tblCities (city_name) VALUES ('A?r?')
INSERT INTO tblCities (city_name) VALUES ('Amasya')
INSERT INTO tblCities (city_name) VALUES ('Ankara')
```

Let's share the visualization of the created cities table. Now, let's create the Districts table.

Districts Table
```sql
CREATE TABLE tblDistricts (
district_ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
city_ID INT,
district_name VARCHAR(255)
)

INSERT INTO tblDistricts (city_ID, district_name) VALUES (1, 'Seyhan')
INSERT INTO tblDistricts (city_ID, district_name) VALUES (1, 'Ceyhan')
... (Other district entries)

Let's share the visualization of the created districts table. Now, let's write all districts corresponding to each city in a single record.

STUFF Query
```sql
SELECT A.city_ID, A.city_name AS City,
STUFF(
(
SELECT ', ' + district_name
FROM tblDistricts AS B
WHERE A.city_ID = B.city_ID
FOR XML PATH('')
),
1, 1, ''
) AS Districts
FROM tblCities AS A
```

Let's share the visualization of the results of our code. Have a great day of querying!

Source: [https://www.ontedi.com/sql/sql-serv...rden-fazla-kaydi-tek-satirda-gosterme-islemi)