How to generate a hyperlink tag to each string in column, to create a tag cloud

Question

I have a table in SQL Server that stores info from a blog, so each record is a post. One of the columns stores the tags related to each post.

I need to create a tag for each tag stored in that column, they are separated with a comma ','.

For example: For the record 1, the column "tags" stores "cars,planes,boats".

I need to generate a column upon SELECT command that will write this:

<a href="blog-tags.aspx?tag=cars">cars<a><a href="blog-tags.aspx?tag=planes">planes</a><a href="blog-tags.aspx?tag=boats">boats</a>

Any help would be highly appreciated! Thanks

Answer 1

If there's some kind of coding layer between the data and the user, then it's probably best to do this in the coding layer.

If you really must do it in sql, you could first convert the column into a separate temporary table, then join with the temporary table (and select accordingly)

INSERT INTO #tempTable(primaryKey, data)
SELECT yt.primaryKey, s.data
FROM 
   YourTable yt
inner join dbo.Split(yt.tags, ',') s

Relies on a split function such as found here: split function (which isn't a very fast one... but will suffice)

Then....

Select yt.*, 
'<a href="blog-tags.aspx?tag=' + t.data + '">' + t.data + '<a>' as Link
from 
    YourTable yt
inner join #tempTable t on yt.PrimaryKey = t.PrimaryKey