Recently I found myself needing to aggregate my multiple row SQL data into a single row. This was because the parent data was more useful to me than the multiple row’d data, but I still needed to include it because it was still an essential part of my report. The data looked something like the following:
I have a database that stores multiple borrowers for a loan in a BORROW table. The BORROW is linked to the LOAN table by FileID field. One loan can have multiple borrowers. Each borrower can also have a spouse who is also a borrower. I need to create a report that identifies BORR1, BORR2.BORR6 information (fn, ln, ssn, etc.) in one row. The major limitation of transposing rows into columns using T-SQL Cursor is a limitation that is linked to cursors in general – they rely on temporary objects, consume memory resources and processes row one at a time which could all result into significant performance costs.
After searching the Oracle documentation I came across the LISTAGG function which took care of exactly what I needed.
Running the above query gave me a result set that looked like the following:
Just like that I was able to see all the departments each one of my people were a part of.
Now there could be a scenario for whatever reason, maybe bad data, where there may be duplication and a person falls into a department more than once. Using an Oracle regular expression like below, you can get rid of all the duplicate entries.
Now there is another function for Oracle that worked equally well, if not better. XMLAgg will do the same task and may play nicer with longer strings of data.
The above code will work with two tables. All of the person’s departments will be concatenated into a single column for each row of person data.
If you ever find yourself needing to work with multiple rows of data, maybe in a sub-query, LISTAGG and XMLAGG are great functions for the job.
I need a way to roll-up multiple rows into one row and one column. In essence I needed to convert something that looked like this:
Into something that looks like this:
SubCategories formatted data
The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.
FOR XML PATH Clause
Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row. In order to create a XML string you must append the FOR XML command after any regular query. There are four options to append to the FOR XML PATH command which will alter what the result will look like. These options are either RAW, AUTO, EXPLICIT, or PATH. For this solution we will use the PATH option (will explain these four option in more detail in a later post).
The following query shows a regular query which retrieves the subcategory name in an XML string
SELECT Name from[Production].[ProductSubcategory]FORXML PATH(');
The results look like the following:
You will notice that you get a formatted XML string without a root node. To convert this list to a CSV string we just need to append a comma (“,”) to the name field.
Sql Concatenate Multiple Rows Into One Row
The following sql query demonstrates the XML string being converted to a CSV string:
SELECT','+Name from[Production].[ProductSubcategory]FORXML PATH(');
This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove. In order to do this we will use the t-sql STUFF function
Sql Combine Multiple Rows Into 1
The syntax of the t-sql STUFF function looks like the following:
To remove the first character of the string all we need to so is do something like the following:
This says, starting with the first
1 character place, replace 1 characters with nothing
What does the final solution look like?
FROM Production.ProductSubcategory SUB
- SubCategories formatted data