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

Problem

I need a way to roll-up multiple rows into one row and one column.  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:

This is one example of what we want the end result to look like:

The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results.  The commands to be used are STUFF and FOR XML.

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

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

KEITH;STEFAN;EDUARD;BRAD

FOR XML Clause

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('')

Putting It All Together

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

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

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.