Turning a Column into a List

I have seen this done so many different ways, most of them badly or at least not very elegantly.

So what is the problem?

Well, it is simple enough. You have a recordset that has a column that you need (for whatever reason) to turn into a comma separated list.

For the purposes of this example, I am going to be working with just a simple single column table. You can knock it up with the following code

-- Suppress data loading messages
SET NOCOUNT ON
-- Create Sample Data

CREATE TABLE Info
(
ID int
)

-- Load Sample Data
INSERT INTO Info VALUES ( 1 )
INSERT INTO Info VALUES ( 2 )
INSERT INTO Info VALUES ( 3 )
INSERT INTO Info VALUES ( 5 )
INSERT INTO Info VALUES ( 8 )

The most common way I have seen is the following

DECLARE @str VARCHAR(max)

-- Coalesce into a nice string

SELECT

@str = COALESCE(@ str, '') + cast(ID as varchar(20)) + ','
from Info

-- Remove the trailing comma.
SET @str = LEFT(@str, LEN(@str) - 1)

SELECT @str

Now, this is good, but it always looks just a bit messy. Having to trim that last comma off is just unnecessary if you think about it for a bit. Now that your really thinking about it, you will also notice that this will die badly if our table is empty as the LEFT will just collapse.

So, lets write something simpler…

DECLARE @str varchar(max)

SELECT @str = COALESCE(@str + ', ', '') + CAST(ID as VARCHAR) FROM Info
SELECT @str

Now wasn’t that much neater?

Advertisements

Microsoft Case Studies

Well I guess I can finaly talk about some of the work I have been doing since I left RedBalloon as Microsoft have just posted case studies about them both.

The first is a Silverlight application that we built for the Orlando Magic.

We were working with our team in Orlando to see if it could be done. When a new technology come out, it is good to give it a whirl,so we did…
Read Microsoft’s case study about the Orlando Magic

and the second is a WPF/WCF App for the World Olympian Alliance. As WPF has been around for a while longer than Silverlight, this was a bit easier. We worked with our team in Amsterdam to come up with a sweet little desktop app that puts a huge chunk of community tools in one spot.
Read Microsoft’s Case study about World Olympian Alliance

So that is the first few months of the year… Now it is working on SSPN, a flex and flash app which I am sure I will talk about more later on this year…

Flashes CrossDomain DTD

Why is it that the DTD for the cross-domain policy file doesn’t contain a definition for the ‘to-ports’ attribute when it is clearly recommended in the Flex 2 docs??

Surly it is not too hard to add to add one little snippet to the DTD??

Doesn’t take much, and then my nice IDE (that understands such things as DTDs) would not constantly warn me that the attribute is not declared.

I know, it is a small thing but it is the only warning in my entire app! It would be nice if it would just go away…

Script your SQL DB creation

I have found that there is a never ending struggle to get over the hurdle of actually launching a site. You know how this story goes – there is a dev db full of dev content and you might just copy that DB to put into your staging environment (or you might even use the same DB for both environments) but when it comes to putting the system live, there is never an easy way to clean out all that demo content and get the real stuff in there.

I have often written page after page of CF or SQL code to get over this initial DB population hurdle. Having scripts of the DB mean that I can drop and recreate the database at a moments notice and know that i have a stable DB to go with my stable release, but there is the pain of creating those DB scripts in the first place. I know that MSSQL lets you make the table creation scripts easily but what about their content?

Enter SQLScripter, a nice little .net tool to dump the contents of your database down to sql scripts, all in one hit. Each table becomes their own script and you can quickly repopulate your DB by running them all!

Speaking at WebDU 2007

Once again the great folks at Daemon are putting on webDU 2007 and Geoff dropped me a line to say that I was going to be speaking once again at this great conference.

I will be speaking about Instant messaging and CF, so get ready for some little bots answering silly questions over IM as I prepare!

If IM is not your thing, I will be sure to see you in a bar somewhere to chat about anything, it was a blast last year and I am sure it will be again this year.

WebDU Banner

GoDo New Zealand

Well after a few hard working months at work we have made it to another website launch. I know there will be a formal launch sometime soon but I wanted to shout about it a little bit earlier!

GoDo New Zealand has been consuming most nights and many long days for the last two months.

We took our existing one country infrastructure (from GoDo Australia) and spend a huge chunk of time modifying the core elements to handle multiple countries and such.

We could have gone down the easy route and just copied the db structure and the codebase but then there is the maintainability of the two apps etc…
Anyway, that is what i have been doing these last few months and hence just a little bit quiet

Getting more people to your blog

I often get asked how can I get more people to look at my blog.

The best way to get more people to see it is to get other sites to link to your site.

There are a few ways to do this.
1. Interact with other blogs. Take the blogs that you yourself read and comment on some of their cooler posts, and when you do make sure that you put in a link back to your site so that people reading your comment might follow it back to our site.
2. A similar thing works for forums or other public internet areas (like openBC or linkedin), post a comment or an entry that people might read and be sure to put a link into your site.
3. the last is less dignified, it involves begging others to put links to your site on their site.

The internet is very much like any other real world marketing landscape, you have to advertise that your site (or blog) exists and the cheapest way is the internet equivalent of graffiti or writing on a wall.