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