For those of you who are also into SQL Server and haven’t checked out www.SQLShare.com yet, you really should, if for nothing else but the daily dose of SQL tidbits you can get in your email box. Each workday a different video tip is posted on a SQL Server related topic. You may not be interested in every one, but I’m sure you will find a couple each week that you will want to watch. Most are only about 2-3 minutes and therefore don’t take a lot of time out of your day. Occasionally, there may be one or two that are between ten and fifteen minutes.
On today’s video, Plamen Ratchev did a short (less than 3 minute) presentation on concatenating the values from a single column across the rows in a table using: FOR XML PATH. It was an interesting technique, but not a lot of people have really dived that much into XML or the various related commands to have found this technique. What I’m going to describe here is another method that I use when creating dynamic pivot tables in SQL Server. It uses the COALESCE function.
First you need to build a sample table. I’ll use the same code as Plamen to build a simple product table:
Create Table Products ( sku INT PRIMARY KEY, product_desc varchar(35)); INSERT INTO Products VALUES (1, 'Book'); INSERT INTO Products VALUES (2, 'Magazine'); INSERT INTO Products VALUES (3, 'DVD'); INSERT INTO Products VALUES (4, 'Video'); INSERT INTO Products VALUES (5, 'CD'); INSERT INTO Products VALUES (6, 'Map');
Next, you need to write the code to build a comma delimited string that includes the values from one of the columns in the table. The technique I use here is similar to the one that I use to build a comma delimited list of field names to create a dynamic Pivot statement. It involves the use of a temporary string variable in which I will progressively build the string value and the COALESCE command. The code you need is here:
DECLARE @Product_String varchar(8000) SELECT @Product_String = COALESCE(@Product_String + ', ', '') + Product_Desc FROM ( SELECT Product_Desc FROM Products ) AS A ORDER BY A.Product_Desc PRINT @Product_String
The COALESCE command is a simple command that returns the first non-null value from a set of arguments. I use it in the SELECT statement because it will automatically combine each of the fields and I can add commas between each value that it adds. To begin the process, I define a temporary variable, @product_string which I declare, but do not initialize. This means the initial ‘value’ of the variable is NULL. Then I use the COALESCE statement with two parameters, one is @product_string and the other is an empty string ‘’. The first time through the SELECT, @product_string is null, so the function returns the empty string. I then concatenate the result of the COALESCE function with the value of the column I’m interested in. This is result is saved back into @product_string.
For the rest of the passes through the table, COALESCE function adds the the next product name value and the comma separator until the end of the table in this case. (Of course, you could limit the result by using a WHERE clause.)
The final result is shown here:
Book, CD, DVD, Magazine, Map, Video
While this string is comma delimited, it does not place the text values in quotes. However, that can easily be fixed by using the following variation of the SELECT statement to add quotes to the individual values.
SELECT @Product_String = COALESCE(@Product_String + ', ', '') + '"' + Product_Desc + '"' FROM ( SELECT Product_Desc FROM Products ) AS A ORDER BY A.Product_Desc PRINT @Product_String
Which results in:
"Book", "CD", "DVD", "Magazine", "Map", "Video"
Hope you found that interesting. Remember to check out www.SQLSHARE.COM to get your daily dose of SQL tips.