Combining rows into comma delimited string

A frequent task in reporting is to combine rows together into a comma delimited string.  While this can be done in a loop or a cursor, there are other ways to perform this task.  If you are only dealing with a single result, there is a quick way to accomplish this with some shorthand:

/*
set up some data
*/

	declare @t table (id int identity(1,1), data varchar(50))
		insert into @t(data)
		values
			('a'),('b'),('c')

/* use a variable to roll up the data */

	declare @mystring varchar(100) = ''

	select
		@mystring = @mystring + ',' + data
	from @t

	select substring(@mystring, 2, len(@mystring)) --get rid of the first comma using SUBSTRING()
	select stuff(@mystring, 1, 1, '') --get rid of the first comma using STUFF()

The output in this case is “a,b,c”, combining the data from the 3 rows into one comma delimited string. Instead of writing the code to loop through the rows and append to the string, we simply have to remove the initial comma before returning the results. This example shows 2 ways to do that, using SUBSTRING() and using STUFF(). While this is a nice shortcut, this doesn’t handle all of the cases that we will be presented with over time. Specifically, we will again need to employ a loop if we need to group these strings in some manner. As it turns out, there is a method by which you can do this without the loop – use XML and STUFF().

To start, lets perform the same task as above, this time using XML and STUFF().

	declare @t table (id int identity(1,1), data varchar(50))
		insert into @t(data)
		values
			('a'),('b'),('c')

		select
			stuff
			(
				(
				select
					',' + data
				from @t
				for xml path('')
				)
			, 1, 1, '') as mystring

Our output in this case is the same: “a,b,c”.  Breaking this down, we can see that we are selecting all of the rows from the temp table.  The next step is to append a comma to the beginning of each piece of data.  Then, take this data and roll it up into XML, which will bring the data onto a single row.  The role that STUFF() plays here is the same as in the first example – removing the initial comma. We used STUFF() in this case instead of SUBSTRING() because the data is XML. In order to use SUBSTRING(), we would have first had to convert the XML back to VARCHAR.

Now, we can look at an example where we have multiple members that will each have their own comma delimited string.  For this example, we will call that our “grouping key”.

	declare @t2 table (id int identity(1,1), grouping_key int, data varchar(50))
		insert into @t2(grouping_key, data)
		values
			(1, 'a'),(1, 'b'),(1, 'c'), (2, 'd'), (2, 'e')

	select
		grouping_key,
		stuff
		(
			(
			select
				',' + data
			from @t2
			where
				grouping_key = unique_groups.grouping_key
			for xml path('')
			)
		, 1, 1, '') as mystring
	from
		(
		select
			grouping_key
		from @t2
		) unique_groups
	group by
		unique_groups.grouping_key

What we have done here is to use the XML and STUFF() as an inline function to manipulate the data specific to the row we are returning. As such, the output is not a single value. Rather, it is a table of data with our comma delimited strings broken up into 2 rows by the “grouping key”:

grouping_key mystring
1 a,b,c
2 d,e

One quick note on using XML in this manner – if your data has an ampersands in it, you will need to do some replacing to resolve those conflicts (changing “&” back to “&” due to the XML conversion).

Enjoy!

SQL Server 2012 SP1 – MsiInstaller is going crazy!

Woah! I just installed SQL Server 2012 SP1 and MsiInstaller is attempting to reconfigure management studio constantly!

Event Viewer: Windows Installer reconfigured the product. Product Name: SQL Server 2012 Management Studio. Product Version: 11.1.3000.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 0.

If this is happening to you, the good news is, there is a hotfix. I have had this happen on one of my SQL 2012 SP1 VMs, but I haven’t been able to exactly replicate how the issue came about. The issue has more documentation on Connect, and is resolved by requesting SQL Server SP1 CU2.