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
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.
Some more details about the commands used above can be obtained from MSDN using the links below:
STUFF (Transact-SQL)
FOR XML (SQL SERVER)