Commercial real estate data is typically stored at a very granular level. If your database has a table containing unit data or step rents, it will have a corresponding row for each unit or for the details of each step rent. When you need to group and summarize numerical data, the SQL Group By statement helps you aggregate the data on categories such as MAX, MIN, SUM, and AVG. But how do you group your text fields? In this article, we explore how to show multiple units in a single comma-separated line with SQL Stuff command.
Enter the SQL Stuff Command
So what is the SQL Stuff command? Here’s how it works.
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position. It is often used to concatenate values from multiple rows into a single column. The syntax of the SQL Stuff command is as follows:
STUFF (character_expression, start, length, replaceWith_expression)
- character_expression: The string that will be modified.
- start: The starting position from where the characters will be deleted.
- length: The number of characters to delete from the starting position.
- replaceWith_expression: The string that will be inserted at the starting position.
SQL STUFF() returns a new string that has replaced the specified portion of the original string with the new string.
Let’s use this command to group multiple rows into a single row. In our SQL Fiddle environment, we will first create a dummy table and load it with a few rows of data. Next, we will write a SQL command that utilizes STUFF() to return our desired results.
The Results
In our final results, you will see the distinct ReportId’s were grouped together, and the emails were all put into a single line separated by commas. This is wildly helpful for rent roll and lease abstract reporting.
In Summary
Go try it yourself with this SQL Fiddle example to start! Feel free to reach out if you have any questions. We’re always happy to help!
For more tips, be sure to check out our other blog posts.
Show Multiple Units in a Single Comma Separated Line with SQL Stuff Command