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

Problem

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):

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:

The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results. The SQL Server T-SQL commands used are STUFF and FOR XML.
The T-SQL STUFF command is used to concatenate the results together. In this example, a semi-colon is used as a separator for the results.
The FOR XML option for the SELECT command has four options (i.e. RAW, AUTO, EXPLICIT or PATH) to return the results. In this example, the PATH parameter is used to retrieve the results as an XML string.

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):

character_expression: string to be manipulated
start: initial position to start
length: number of characters to be manipulated
replaceWith_expression: characters to be used

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

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:

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

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

Take this further and create simple queries and then deepen the complexity of the code.
Explore the commands used in this tip further to see what other things you might be able to do.
Some more details about the commands used above can be obtained from MSDN using the links below:

Resources

Community

Subscribe