Friday, April 22, 2011

Concatenate values with FOR XML PATH

In this example I will show you how to use the FOR XML PATH clause to perform row concatenation, in v2005 and v2008.  Let's first create a 'Media' table, and insert a few records:


CREATE TABLE dbo.Media (
  media_id INT PRIMARY KEY,
  media_type VARCHAR(35)
  );


INSERT dbo.Media VALUES (1,'Catalog');
INSERT dbo.Media VALUES (2,'Book');
INSERT dbo.Media VALUES (3,'Magazine');
INSERT dbo.Media VALUES (4,'DVD');
INSERT dbo.Media VALUES (5,'Poster');


Of course, our data is in the table in this format:

  media_id       media_type
   1                   Catalog
   2                   Book
   3                   Magazine
   4                   DVD
   5                   Poster


We want to return all media type values back in a single line, so we just select media_type from the table, and order the result set by media_type, using FOR XML PATH.  We don't want to actually generate the XML, so we place an empty element in the PATH, and because we've added the comma separator, it forces SQL to concatenate the XML as a plain string:

   SELECT ', ' + media_type
   FROM dbo.Media
   ORDER BY media_type
   FOR XML PATH('');

Results:             , Book, Catalog, DVD, Magazine, Poster

Now we just need to use the STUFF function to get rid of that first comma, like this:

    SELECT STUFF((
    SELECT ', ' + media_type
    FROM dbo.Media
    ORDER BY media_type
    FOR XML PATH('')),1, 1, '');

Results:               Book, Catalog, DVD, Magazine, Poster


No comments:

Post a Comment