Rolling up multiple rows into a single row and column for SQL Server data

I need a way to roll-up multiple rows into one row and one column value as a means of concatenation in my SQL Server T-SQL code. I know I can roll-up multiple rows into one row using Pivot, but I need all of the data concatenated into a single column in a single row. In this tip we look at a simple approach to accomplish this.

Solution

To illustrate what is needed, here is a sample of data in a table from SQL Server Management Studio (SSMS):

Users per sector sales

This is an example of rolling up multiple rows into a single row. This is what we want the end result set to look like:

SQL Server T-SQL code to create the above result set by rolling up multiple rows into a single row using FOR XML PATH and the STUFF function:

SELECT SS.SEC_NAME, STUFF((SELECT '; ' + US.USR_NAME FROM USRS US WHERE US.SEC_ID = SS.SEC_ID FOR XML PATH('')), 1, 1, '') [SECTORS/USERS] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME ORDER BY 1

Continue reading this SQL tutorial to learn about additional options and explanations for rolling up multiple rows into a single row.

How to Concatenate Multiple Rows into a Single Row in Microsoft SQL Server

Rolling up data from multiple rows into a single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by:

Check out the example below to walk through the code samples and final solution to roll-up multiple rows into a single row in SQL Server.

Preparing Sample Data

Before we begin, we'll create some tables and sample data which the following script will do for us.

CREATE TABLE SALES_SECTORS( SEC_ID INT, SEC_NAME VARCHAR(30)) GO CREATE TABLE USRS( USR_ID INT, USR_NAME VARCHAR(30), SEC_ID INT ) GO CREATE TABLE ADV_CAMPAIGN( ADV_ID INT, ADV_NAME VARCHAR(30) ) GO CREATE TABLE USR_ADV_CAMPAIGN( USR_ID INT, ADV_ID INT ) GO CREATE TABLE SEC_ADV_CAMPAIGN( SEC_ID INT, ADV_ID INT ) GO INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 1, 'ENTERTAINMENT' ) INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 2, 'CLOTHES' ) GO INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 1, 'ANDERSON', 1 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 2, 'CHARLES', 1 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 3, 'DANNY', 1 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 4, 'LUCAS', 1 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 5, 'KEITH', 2 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 6, 'STEFAN', 2 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 7, 'EDUARD', 2 ) INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 8, 'BRAD', 2 ) GO INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 1, 'SONY ENTERTAINMENT' ) INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 2, 'BEATS SOUNDS' ) INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 3, 'BOOSE' ) INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 4, 'POLO RALPH LAUREN' ) INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 5, 'LACOSTE' ) GO INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 1 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 2 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 2 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 3 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 3, 3 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 4, 2 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 5, 4 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 6, 5 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 7, 4 ) INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 8, 5 ) GO INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 1 ) INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 2 ) INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 3 ) INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 4 ) INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 5 ) GO

SQL Server STUFF() Function

Before going to the examples, we need to understand the workings of the commands mentioned above. The STUFF() function puts a string in another string, from an initial position. With this we can insert, replace or remove one or more characters.

This syntax is STUFF(character_expression, start, length, replaceWith_expression):

Here is an example of the how to use the STUFF command.

For our example we have a single string that looks like this:

;KEITH;STEFAN;EDUARD;BRAD

We want to remove the first ; from the list so we end up with this output:

KEITH;STEFAN;EDUARD;BRAD

To do this we can use the STUFF command as follows to replace the first ; in the string with an empty string.

SELECT STUFF(';KEITH;STEFAN;EDUARD;BRAD', 1, 1, '')

And this returns this output as a concatenated string:

KEITH;STEFAN;EDUARD;BRAD

FOR XML Clause for the SQL Server SELECT Statement

The FOR XML clause, will return the results of a SQL query as XML. The FOR XML has four modes which are RAW, AUTO, EXPLICIT or PATH. We will use the PATH option, which generates single elements for each row returned.

If we use a regular query such as the following it will return the result set shown below.

SELECT SS.SEC_NAME, US.USR_NAME FROM SALES_SECTORS SS INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID ORDER BY 1, 2

Users per sector sales

If we take this a step further, we can use the FOR XML PATH option to return the results as an XML string which will put all of the data into one row and one column.

SELECT SS.SEC_NAME, US.USR_NAME FROM SALES_SECTORS SS INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID ORDER BY 1, 2 FOR XML PATH('')

SQL STRING_AGG Function to Rollup Data

In SQL Server 2017, the STRING_AGG function was introduced as a new option to rollup data. Check out these tips to learn more:

SQL Server Example to Rolling up Multiple Rows into a Single Row

Example 1

Now that we see what each of these commands does, we can put these together to get our final result.

The example query below uses a subquery where we are returning XML data for the USR_NAME from table USRS and joining this to the outer query by SEC_ID from table SALES_SECTORS. For each value from the inner query, we are concatenating a ";" and then the actual value to have all of the data from all rows concatenated into one column. We are grouping by SEC_NAME to show all USERS within that SECTOR.

SELECT SS.SEC_NAME, (SELECT '; ' + US.USR_NAME FROM USRS US WHERE US.SEC_ID = SS.SEC_ID FOR XML PATH('')) [SECTORS/USERS] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME ORDER BY 1

The below is the output for this query. We can see that we have the leading; in the SECTORS/USERS column which we don't want.

In this modified example, we are now using the STUFF function to remove the leading ; in the string.

SELECT SS.SEC_NAME, STUFF((SELECT '; ' + US.USR_NAME FROM USRS US WHERE US.SEC_ID = SS.SEC_ID FOR XML PATH('')), 1, 1, '') [SECTORS/USERS] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME ORDER BY 1

And we get this result set:

If we also want to order the SECTORS/USERS data we can modify the query as follows:

SELECT SS.SEC_NAME, STUFF((SELECT '; ' + US.USR_NAME FROM USRS US WHERE US.SEC_ID = SS.SEC_ID ORDER BY USR_NAME FOR XML PATH('')), 1, 1, '') [SECTORS/USERS] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME ORDER BY 1

Example 2

If we want this all to be in one column, we can change the query a little as follows:

SELECT SS.SEC_NAME + ': ' + STUFF((SELECT '; ' + US.USR_NAME FROM USRS US WHERE US.SEC_ID = SS.SEC_ID FOR XML PATH('')), 1, 1, '') [SECTORS/USERS] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME ORDER BY 1

And this gives us this result:

Users concatenated by sale sectors

Example 3

This example takes it a step further where we have multiple subqueries to give us data based on USERS within CAMPAIGNS within SECTORS.

SELECT SS.SEC_ID, SS.SEC_NAME, STUFF((SELECT '; ' + AC.ADV_NAME + ' (' + STUFF((SELECT ',' + US.USR_NAME FROM USR_ADV_CAMPAIGN UAC INNER JOIN USRS US ON US.USR_ID = UAC.USR_ID WHERE UAC.ADV_ID = SAC.ADV_ID FOR XML PATH('')), 1, 1, '') + ')' FROM ADV_CAMPAIGN AC INNER JOIN SEC_ADV_CAMPAIGN SAC ON SAC.ADV_ID = AC.ADV_ID AND SAC.SEC_ID = SS.SEC_ID ORDER BY AC.ADV_NAME FOR XML PATH('')), 1, 1, '') [CAMPAIGNS/USERS PER SECTOR] FROM SALES_SECTORS SS GROUP BY SS.SEC_ID, SS.SEC_NAME

Users/Campaigns concatenated by sale sectors

Example Rolling Up Index Columns into One Row

Here is an example that will rollup indexes into one row and show the columns that are part of the index as well as included columns if any exist.

SELECT SCHEMA_NAME(ss.SCHEMA_id) AS SchemaName, ss.name as TableName, ss2.name as IndexName, ss2.index_id, ss2.type_desc, STUFF((SELECT ', ' + name from sys.index_columns a inner join sys.all_columns b on a.object_id = b.object_id and a.column_id = b.column_id and a.object_id = ss.object_id and a.index_id = ss2.index_id and is_included_column = 0 order by a.key_ordinal FOR XML PATH('')), 1, 2, '') IndexColumns, STUFF((SELECT ', ' + name from sys.index_columns a inner join sys.all_columns b on a.object_id = b.object_id and a.column_id = b.column_id and a.object_id = ss.object_id and a.index_id = ss2.index_id and is_included_column = 1 FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.objects SS INNER JOIN SYS.INDEXES ss2 ON ss.OBJECT_ID = ss2.OBJECT_ID WHERE ss.type = 'U' ORDER BY 1, 2, 3

Conclusion

There are always several options to complete a task within SQL Server and we should take the time to explore the capabilities offered by the database before developing large and complex code. I hope this is one more of those examples that shows there are sometimes easier approaches than you think might be available.

Next Steps