Sql Multiple Rows Into One Row



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.

firstnamelastnamedepartment
NicRaboyEngineering
MariaCamposEmergency Room
NicRaboyOperations
NicRaboyDesign

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:

Sql server multiple rows into one row
firstnamelastnamedepartments
NicRaboyDesign,Engineering,Operations
MariaCamposEmergency Room

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.

Nic Raboy

Sql Multiple Rows Into One Row

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in Java, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Apache Cordova. Nic writes about his development experiences related to making web and mobile development easier to understand.

Please enable JavaScript to view the comments powered by Disqus.

The Problem:

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:


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:

Sql Multiple Rows Into One Row
STUFF(character_expression,start,length,replaceWith_expression)
To remove the first character of the string all we need to so is do something like the following:
2
4
6
8
10
GO
SubCatName=
FROM Production.ProductSubcategory SUB
SUB.ProductCategoryID=CAT.ProductCategoryID
),1,1,')
SubCategories formatted data

References